TRIGGERS

Question

Post reply

TRIGGERS

Venkat Jonnalagadda

Mr or Mrs. 500

Points: 510
More actions
April 8, 2013 at 9:48 pm

#276183

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:

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

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 1

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 !!!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

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/

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 3

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 !!!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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/

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 5

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 !!!

nenad-zivkovic Default port Points: 1448 More actions · Answer 6

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)

call.copse SSCoach Points: 18048 More actions · Answer 7

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

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.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 9

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 !!!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

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/

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 11

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,

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 12

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 !!!

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 13

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

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 14

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