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.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
0
ZberteocCommented:
The insert/update/delete operations are done by using in the background 2 internal tables: inserted and deleted having both identical structure with the table is being operated.

1. Insert: On insert operation the deleted table is not used so it will have no rows, only the inserted table will have rows with the new data that is provided.

2. Update: on update operation both inserted and deleted tables are used because in the background SQL engine does updates in 2 steps: first deletes the existing row and then inserts a new one with the new data. The deleted in this case is used to keep the removed data in case of rollback or audit operations.

3. The delete operation uses only the deleted table where it keeps the data removed until the operation is committed.

In order to check if the inserted(new data) data is actually different from the old data you can do this:

select * from inserted
except
select * from deleted

except statement only returns a row if there is a difference between the 2 sets from the above and bellow query. If the data is identical returns nothing. This is the best way to check if you really need to do something. Keep in mind that the UPDATE operation will still be performed in the case the inserted data is identical with the removed data but you might not need to do anything in the trigger in this case. The except however will fail if you use any "incomparable" data type i.e. spatial, geometry and geography, or XML. In that case you will have to use an explicit column list instead of * and make sure you cast/convert those types to something that can be compared, like varchar or nvarchar.

Having said that your question is still confusing and we don't know what exactly you want to do.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
@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

0
ZberteocCommented:
Well, what do "work" and "doesn't work" mean? Do you get any errors? Do you get unwanted results? Some examples would help.
0
Anoo S PillaiCommented:
The following two lines are causing the behavior.

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

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. Since the condition "@@ROWCOUNT = 0" becomes true, the trigger stops further execution.

NOTE:- I think you are more or less interested in understanding the reason behind the behavior , not the functional viability of your solution
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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.
0
Anoo S PillaiCommented:
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.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
@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
0
ZberteocCommented:
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.
0
Anoo S PillaiCommented:
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.
0
ZberteocCommented:
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.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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.
0
ZberteocCommented:
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.
0
ZberteocCommented:
However the unicity of a values in a column(s) should not be dealt with in triggers but rather with unique constraints.
0
Scott PletcherSenior DBACommented:
@@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.]

I prefer to just use EXISTS() against the pseudo-tables:

IF NOT EXISTS(SELECT TOP (1) * FROM inserted)
AND NOT EXISTS(SELECT TOP (1) * FROM deleted)
    RETURN
0
Anoo S PillaiCommented:
@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 :(
0
ZberteocCommented:
"@@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.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I will go though all this weekend I guess after I am done with another task. There are lots for me to absorb.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
@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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.