Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

DML Trigger

The following trigger functions fine without lines 10, 11, 15 and 16 runing:

INSERT INTO Production.Categories (categoryname,description)
VALUES ('TestCategory1', 'Test1 description v1');

* It also runs okay with lines 10 and 11 but allows duplicate entries which is not acceptable.
* While it works fine with lines 15 and 16 but disallows duplicate entries, as expected.

Question: Why with lines 10 and 11 fails but not with lines 15 and 16?

IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL
DROP TRIGGER Production.tr_ProductionCategories_categoryname;
GO
CREATE TRIGGER Production.tr_ProductionCategories_categoryname
ON Production.Categories
AFTER INSERT, delete, UPDATE
AS
BEGIN
-- runs okay with lines 10 and 11 but allows duplicate entries which is not acceptable.
-- Select * From Inserted;   
-- Select * From deleted;     
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- it works fine with lines 15 and 16 but disallows duplicate entries, as expected.
Select * From Inserted;  
Select * From deleted;    
IF EXISTS (SELECT COUNT(*)
FROM Inserted AS I
JOIN Production.Categories AS C
ON I.categoryname = C.categoryname
GROUP BY I.categoryname
HAVING COUNT(*) > 1 )
BEGIN
THROW 50000, 'Duplicate category names not allowed', 0;
END;
END;

Open in new window

I know that the following lines are deprecated and not used in the recent versions of SQL Server.

Select * From Inserted;
Select * From deleted;

But I wonder why they work in one place but not another. FYI, I M using SQL Server 2014.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Sorry but I don't have any MSSQL 2014 instance to test it.
What are you trying to do? If you want to prevent duplicate values then you should do it with a constraint and not with triggers.
By the way, deleted table doesn't exist when the operation is an insert.
Avatar of Mike Eghtebas

ASKER

re:> What are you trying to do?
Trigger functions okay. I am just trying different things to see why shifting lines 15 and 16 to the position at lines 10 and 11 fails.

re:> By the way, deleted table doesn't exist

Do not agree on this. deleted exists even without the highlighted parameters (I tested).
AFTER INSERT --, delete, UPDATE  

In the case of Insert operation the deleted  table has zero rows.

Mike
SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Zberteoc,

Thank you for the good summary.

My question is so simple that tends to be confusing I guess: I have 3 codes below titled A, B and C. A and B work but C doesn't: (at this point I am happy with responses I have; I have added this post to clarify what my original question was. I guess I have done poorly describing my question).

A:
IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL
DROP TRIGGER Production.tr_ProductionCategories_categoryname;
GO
CREATE TRIGGER Production.tr_ProductionCategories_categoryname
ON Production.Categories
AFTER INSERT, delete, UPDATE
AS
BEGIN
-- runs okay with lines 10 and 11 but allows duplicate entries which is not acceptable.
-- Select * From Inserted;   
-- Select * From deleted;     
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- it works fine with lines 15 and 16 but disallows duplicate entries, as expected.
-- Select * From Inserted;  
-- Select * From deleted;    
IF EXISTS (SELECT COUNT(*)
FROM Inserted AS I
JOIN Production.Categories AS C
ON I.categoryname = C.categoryname
GROUP BY I.categoryname
HAVING COUNT(*) > 1 )
BEGIN
THROW 50000, 'Duplicate category names not allowed', 0;
END;
END;

Open in new window


B:
IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL
DROP TRIGGER Production.tr_ProductionCategories_categoryname;
GO
CREATE TRIGGER Production.tr_ProductionCategories_categoryname
ON Production.Categories
AFTER INSERT, delete, UPDATE
AS
BEGIN
-- runs okay with lines 10 and 11 but allows duplicate entries which is not acceptable.
-- Select * From Inserted;   
-- Select * From deleted;     
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- it works fine with lines 15 and 16 but disallows duplicate entries, as expected.
Select * From Inserted;  
Select * From deleted;    
IF EXISTS (SELECT COUNT(*)
FROM Inserted AS I
JOIN Production.Categories AS C
ON I.categoryname = C.categoryname
GROUP BY I.categoryname
HAVING COUNT(*) > 1 )
BEGIN
THROW 50000, 'Duplicate category names not allowed', 0;
END;
END;

Open in new window


C (the problem with this version it allows multiple inserts thus violating uniquness at column categoryname) :
IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL
DROP TRIGGER Production.tr_ProductionCategories_categoryname;
GO
CREATE TRIGGER Production.tr_ProductionCategories_categoryname
ON Production.Categories
AFTER INSERT, delete, UPDATE
AS
BEGIN
-- runs okay with lines 10 and 11 but allows duplicate entries which is not acceptable.
Select * From Inserted;   
Select * From deleted;     
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- it works fine with lines 15 and 16 but disallows duplicate entries, as expected.
-- Select * From Inserted;  
-- Select * From deleted;    
IF EXISTS (SELECT COUNT(*)
FROM Inserted AS I
JOIN Production.Categories AS C
ON I.categoryname = C.categoryname
GROUP BY I.categoryname
HAVING COUNT(*) > 1 )
BEGIN
THROW 50000, 'Duplicate category names not allowed', 0;
END;
END;

Open in new window

Well, what do "work" and "doesn't work" mean? Do you get any errors? Do you get unwanted results? Some examples would help.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Move the:
SET NOCOUNT ON
to the very start of the trigger.

I very strongly suspect that "select * from ..." commands appearing after that statement won't cause an issue, but could if the selects appear before that SET statement.
A note on my previous comment -

@@ROWCOUNT "Returns the number of rows affected by the last statement." , In this case, it will return the number of rows affected by the statement "Select * From deleted; ", which is obviously 0.
@Zberteoc,

I guess my edidt :C (the problem with this version it allows multiple inserts thus violating uniquness at column categoryname) :" was done after you had read my last post.

@ScottPletcher, Thank you for the post. I will try; most likely that is the cause.

 @Anoo S Pillai,
Thanks for the tips. Yes, I am trying different things to wrap my mind around it. The solution I have but trying to dig a bit further to understand why in some cases it doesn't work.

Mike
The 2 selects in a row like that:

Select * From Inserted;  
Select * From deleted;    

Don't make any sense because only the last one matters so I was not sure what the asker wants. I suspected he needs to know if any inserted vs deleted rows exist and that is why I added the explanation of how they work and suggested the:

Select * From Inserted;  
except
Select * From deleted;    

But of course, it doesn't mean that is actually what he needs.
My point was -
The reason behind your script C allowing duplicate values is that the code comes after the code "IF @@ROWCOUNT = 0 RETURN;" is not at all executed when your "Select * From deleted; " is there.
One think that would help is to explain in words what you want to achieve like:

on inserts do that

on deletes do that

on updates do that

and what are the constraints you are looking into, like avoid duplicates, etc. Just saying "it works or not" doesn't help if we don't know what are you after. As you can see everybody guesses what you want at best.
I just wanted to know why code "C" fails. By that I mean why it allows the code below to insert the same categoryName multiple times if user tries to execute it multiple times:

INSERT INTO Production.Categories (categoryname,description)
VALUES ('TestCategory1', 'Test1 description v1');

Whereas codes "A" and "B" allow this code to run only once. If user tries for a second time, it returns Duplicate category names not allowed as expected to maintain uniqueness at this column.
In the case of code A the only thing that will prevent insert or update si the IF EXISTS (SELECT COUNT(*) condition. The IF @@ROWCOUNT = 0 RETURN I don't think will have an effect because in that scope there is no statement that will affect any numbers of rows. and by default returns 1 IF IT COMES BEFORE "set nocount on"! If it comes after it will return 0 because set necount statement doesn't affect any rows. So in the case of A The IF @@ROWCOUNT = 0 RETURN has NO EFFECT.

In the case of code B is a similar situation. IF @@ROWCOUNT = 0 RETURN; is useless because nothing  happens within the scope and it will return 1. and only the IF EXISTS (SELECT COUNT(*) matters but that has NOTHING TO DO with the 2 selects!

In the case of C

Select * From deleted;    
IF @@ROWCOUNT = 0 RETURN;

will cause to exist (RETURN) if it is an insert operation(the deleted has no rows so @@ROWCOUNT will be always 0) so it will NEVER get to the  IF EXISTS (SELECT COUNT(*) so the insert will succeed.
However the unicity of a values in a column(s) should not be dealt with in triggers but rather with unique constraints.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@eghtebas - Did you go through my previous comments ? I was just trying to explain "why code "C" fails ".

The following is the behavior with trigger C

1) Your INSERT command fires the trigger
2) It reaches line 10  and executes "Select * From inserted; "
3) The variable @@ROWCOUNT is automatically set to 1 ( only one row is inserted by your insert statement and hence inserted table has only that row in it)
3) It goes to line 11 and executes "Select * From deleted; "
4) No rows are available in table 'deleted' , so no rows are selected
5) Since the no. of rows are zero , the variable @@ROWCOUNT is set to 0
6) Trigger reaches line 12 ( IF @@ROWCOUNT = 0 RETURN; )
7) it checks condition @@ROWCOUNT = 0
8) The condition is true, hence the statement RETURN is invoked.
9) RETURN gracefully exits from the trigger and further commands in the trigger are not executed.

ON A and B , the commands checking duplicates are being invoked and in C it is exited from the trigger at line 12.

If this does not clarify your doubt, i doubt my understanding on your question is wrong :(
"@@ROWCOUNT is set by the statement that caused the trigger to fire.  That is, if an UPDATE statement updated 5 rows, upon entry to the trigger, @@ROWCOUNT will be 5.  [MERGE statement is different, which makes me tend away from relying on @@ROWCOUNT anymore.]"

That is correct actually, only if it is placed after SET NOCOUNT ON will return 0. Even so the condition IF @@ROWCOUNT = 0 will never be true in the case of actual insert, regardless of the number of rows so the trigger code will continue after. In the case of code C it will always be True because of the Select * From deleted which affetcs no rows so it will never pas IF @@ROWCOUNT = 0 RETURN;

That is why in case C the IF EXISTS (SELECT COUNT(*) will never happen so it will allow inserts.
I will go though all this weekend I guess after I am done with another task. There are lots for me to absorb.
@Anoo S Pillai,

re:> The @@ROWCOUNT will be set to 0 when you select data from table DELETED , Reason - Since this is an INSERT operation no values would be available in DELETED table...

You are correct. Yes, affter @@ROWCOUNT becomes zero because of Select * From Deleted; it never gets to "THROW 50000, 'Duplicate category names not allowed', 0;" to undo the inserted row for a second time (it just exits the routine leaving offending record intact).

Note: By the time it gets to "IF @@ROWCOUNT = 0 RETURN;" it already has entered a duplicate item because we have defined this trigger as "AFTER INSERT, UPDATE." Only with Instead of trigger the insertion takes place via T-SQL inside the DML trigger. Also note, I have removed Delete from "AFTER INSERT, UPDATE" because it is not applicable in this context.


@Scott,

I had to keep like:

IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;        --<-- moving  this before IF the code fails

Possibly I have not understood your comment. Also, I have the followings from: https://msdn.microsoft.com/en-us/library/ms189837.aspx

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

Thank you for the excellent discussion and education you have provided for me.

Mike