TRIGGERS

  • Comments posted to this topic are about the item TRIGGERS

    ___________________________________________________________________
    If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:

  • I don't see the table definition carefully and choose incorrect option.:angry:

    Any way Good question, keep it up; 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Danny Ocean (4/8/2013)


    I don't see the table definition carefully and choose incorrect option.:angry:

    Any way Good question, keep it up; 🙂

    Same here Vinay, forget about the TEXT datatype part and choose incorrect option 🙁

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (4/8/2013)


    Danny Ocean (4/8/2013)


    I don't see the table definition carefully and choose incorrect option.:angry:

    Any way Good question, keep it up; 🙂

    Same here Vinay, forget about the TEXT datatype part and choose incorrect option 🙁

    :w00t:

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • I'm glad I double and triple checked before replying; I almost fell for it. The glaring error in how the trigger code handles multi-row inserts is so staring people in the eye that it distracts from the actual table creation code.

    It's probably a good thing I have a fair sense of paranoia. I simply had the feeling that there must be something fishy with the question. I first focused in the wrong direction (suspecting some weird behaviour of DATALENGTH with the text datatype), but then luckily remembered the issue with text and triggers.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (4/9/2013)


    I'm glad I double and triple checked before replying; I almost fell for it. The glaring error in how the trigger code handles multi-row inserts is so staring people in the eye that it distracts from the actual table creation code.

    It's probably a good thing I have a fair sense of paranoia. I simply had the feeling that there must be something fishy with the question. I first focused in the wrong direction (suspecting some weird behaviour of DATALENGTH with the text datatype), but then luckily remembered the issue with text and triggers.

    Good one Hugo. congratulation !!!

    I fell that this question try to distract users in different way. It's good question to start our day. 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • I had totally wrong idea why would trigger fail, but based on it - selected what would turn out to be a correct answer 🙂

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • I'm still not 100% if the misdirection was totally intentional, if so it was neatly executed, anyhow I wasn't fooled fortunately.

  • Awesome; thank you for posting.

    I had same issue on the trigger with the same column where I was creating newly; when I literally tested the code even knowing the trigger creation fails - my concerns were completely unacceptable; I was testing it under 2012 express edt, in the answers I was expecting to see "gives error.." option and I started to wonder possibly it might work in previous versions of sql - so i selected random wrong answer to see what is this all about.. and then...oh... c$%^ ... it is not nested sequence, even though it fails... other statements can be executed as usual...

    Another interesting point needs be added to my extraction skills list.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Friends, i have a question. I have made same changes in script and execute it. First have a look on script

    --statement 1

    CREATE TABLE temp

    (

    ID INT NOT NULL,

    MyText varchar(max) NOT NULL

    );

    GO

    --statement 2

    CREATE TRIGGER dbo.CheckTextSize

    ON dbo.temp

    AFTER INSERT

    AS

    IF (SELECT COUNT(*) FROM Inserted WHERE DATALENGTH (MyText) > 20) = 1

    BEGIN

    RAISERROR ('Too large Text Size being inserted', 16, 1);

    ROLLBACK TRAN;

    END

    GO

    --statement 3 (text size being inserted into text column is < 1000000 characters in length)

    INSERT INTO temp

    VALUES (1, 'valid text size')

    GO

    --statement 4 (Assume text size being inserted into text column is > 1000000 characters in length)

    INSERT INTO temp

    VALUES (2, 'invalid text size .......(text size > 1000000 characters)')

    GO

    --statement 5 (Assume text size being inserted into text column is > 1000000 characters in length)

    INSERT INTO temp

    SELECT 3, 'invalid text size .......(text size > 1000000 characters)'

    GO

    --statement 6 (Assume text size being inserted into text column is > 1000000 characters in length)

    INSERT INTO temp

    SELECT 4, 'invalid text size .......(text size > 1000000 characters)'

    UNION ALL

    SELECT 5, 'invalid text size .......(text size > 1000000 characters)'

    GO

    --statement 7

    SELECT * FROM dbo.temp

    GO

    DROP TRIGGER dbo.CheckTextSize;

    GO

    DROP TABLE temp;

    GO

    In script, i have changed data type of column MyText from TEXT to Varchar(MAX). I have also change the condition value in trigger. I replace 1000000 with 20.

    When you execute complete script,

    you will find that Statement 3 (ID 1) execute successfully.

    Statement 4 (ID 2) and Statement 5 (ID 3) failed due to condition in trigger.

    BUT statement 6 (ID 4 & 5) execute successfully and inserted two rows in table. In this we are using UNION ALL.

    How it's possible ? Anyone let me know..

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Danny Ocean (4/9/2013)


    BUT statement 6 (ID 4 & 5) execute successfully and inserted two rows in table. In this we are using UNION ALL.

    How it's possible ? Anyone let me know..

    Take a close look at the actual code in the trigger. It contains a COUNT(*), so it counts the number of rows that satisfy a given condition. In the case of statement 6, both of the inserted rows will qualify, so the COUNT(*) is equal to 2. And the trigger only raises and error and rolls back the transaction if that number is 1.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I am using SQL Server 2005 and changed the column to varchar(100) and changed the limit to 20 in the trigger. Statement 4 (id=2) raised an error and failed. However statement 5 (ids 3 and 4) inserted without raisiing an error and I got three rows back from the select statement. Can anybody explain why?

    Thanks,

  • Hugo Kornelis (4/9/2013)


    Danny Ocean (4/9/2013)


    BUT statement 6 (ID 4 & 5) execute successfully and inserted two rows in table. In this we are using UNION ALL.

    How it's possible ? Anyone let me know..

    Take a close look at the actual code in the trigger. It contains a COUNT(*), so it counts the number of rows that satisfy a given condition. In the case of statement 6, both of the inserted rows will qualify, so the COUNT(*) is equal to 2. And the trigger only raises and error and rolls back the transaction if that number is 1.

    Thanks Hugo 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • The trigger has a condition of if count(*) = 1 so statement 5 works because its count=2

  • It took me a little while to realize the trigger has a condition that count(*) = 1. Since its count = 2, it works. Interesting question.

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply