Typical Trigger Issue
-
Ok, for every item added to one of my tables i wanted to add or update the modifed date column.
I have a default contraint on it when it is first created that sets the value to getdate().
However, the column will not be automatically updated everytime i change the column. For this, I think i need a trigger.
However, triggers fire once per DML operation and not once per affected row.
My book has a subtitle "Developing Multi-Row Enabled Triggers." I don't understand it. 🙂
It gives the following code sample, but i don't know what Deleted and Inserted are or how they work.
ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
SELECT D.LastName + ' changed to ' + I.LastName
FROM Inserted I
JOIN Deleted D
ON I.PersonID = D.PersonID
UPDATE Person
SET LastName = 'Carter'
WHERE LastName = 'Johnson'
I didn't know if this was the solution to my problem or not.
Later under "Recursive Triggers" he talks about triggers that seem to do what i want but has a Trigger_NestLevel() function applied.
Here is the code. I don't fully understand it or why or how it is different from the above concept.
USE OBXKites
ALTER DATABASE OBXKites SET RECURSIVE_TRIGGERS ON
-- assumes created and modified date columns have been added to the Product table.
CREATE TRIGGER Products_ModifiedDate ON dbo.Product
FOR UPDATE
AS
SET NoCount ON
PRINT Trigger_NestLevel()
If Trigger_NestLevel() > 1
RETURN
IF (UPDATE(Created) or UPDATE(Modified))
AND Trigger_NestLevel() = 1
BEGIN
RAISERROR('Update failed.', 16, 1)
ROLLBACK
RETURN
END
/* Update the Modified date */
UPDATE Product
SET modified = getdate()
FROM Product
JOIN Inserted
ON Product.ProductID = Inserted.ProductID
-- end of trigger
goGo
UPDATE PRODUCT
SET [Name] = 'Modifed Trigger'
WHERE Code = '1002'
SELECT Code, Created, Modified
FROM Product
WHERE Code = '1002'
So what are these queries doing and which method should i use for my last_updated column? thanks!
-
Ok, First things first:
This is basically the trigger you need:
ALTER TRIGGER TriggerOne ON YourTable
AFTER Update
AS
Update S set ModifiedDate = GetDate()
FROM YourTable S
JOIN Inserted I
ON I.ID = S.ID
* Noel -
Here is a bit of explanation for you:
Nested triggers is a setting that by default is turned off and what it does is
that if an update happens in the trigger on the table from where the trigger was fired the trigger will be fired again!
-- Up to a max of 32 levels! thats why you need to check for nested_level in the trigger if that option is set to ON
Personally I consider that option to be disabled all the time as a good thing!!
(it can create nasty errors and difficult to debug algorithms. You think GO TOs are bad give this a shot and you will see
) Second
What I wrote is the basic trigger code
Inserted and deleted are nothing but virtual tables build from the transaction log
where inserted represents the image after and deleted the image before the change
(an update is nothing but a delete followed by an insert
)
* Noel -
Thanks again Noel!
Assumption #1
I assumed that using recursive triggers was probably not the best idea since it is so limited. It causes an error when nested_level > 32. I assume then that if the nested_level is not allowed to exceed 32 and there are more than 32 rows needing to be updated, then those rows will not be updated. Further since my modified date column is set to not null, it would probably cause an error resulting in the rows not being saved or the transaction being rolled back. Is this a correct assumption?
Assumption #2
My other assumption is that both of the above triggers will work (the one using inserted and deleted table and the trigger using the recurve trigger). But it is just a matter of preference and best practice.
Inserted and Deleted table Concept
I think i understand the concept of the Inserted and Deleted virtuals tables now, however, i don't understand exactly how the trigger uses these tables to update each row. If it does it after all the rows have been changed, could this cause a failure if the column is left null when it is a NOT NULL column? Or does it update the modified column as the rows are written to (one by one)?
Thanks very much!!!
-
Assumption 1 is incorrect. The nestlevel does not depend on the number of rows affected. You can fire one trigger and have hundreds or thousands of rows in the inserted or deleted tables. You only get nestlevel > 1 if you then update the source table and fire the trigger again.
Yes it does boil down to best practice, but I don't see a reason to use nested triggers in this case. The above example should work fine.
Also, you will not get the trigger fired is there is a constraint violation or other error, like a NOT NULL column being updated to NULL. The trigger fires after the DML is successfully completed.
Dylan Peters
SQL Server DBA -
Thanks very much Dylan! I have a lot to learn. I'm in the process of looking up info so i can understand deleted and inserted tables better. Now i see, i need more work on nested level triggers also. I thought i could avoid them by listing them under bad practices.
Regarding this quote: "Also, you will not get the trigger fired if there is a constraint violation or other error, like a NOT NULL column being updated to NULL. The trigger fires after the DML is successfully completed."
Again, you expose the gaps in my knowledge. I was under the impression that if a row was being updated the trigger on that row would automatically fire. For example: If i have these four columns--ManualID, Title, AuthorID, LastUpdated -- and i updated maybe the title and the authorId, then LastUpdated trigger would automatically fire on that column, for that row. I didn't think i NEEDED to put a value IN LastUpdated FOR THE TRIGGER TO FIRE.
"The trigger fires after the DML is successfully completed." That statement being true, would mean that my example update above would not complete successfully because LastUpdated would be set to NOT NULL. So the update would be rolled back and not commited.
Either these books are incomplete or i have a wild imagination!
Can somebody explain?
Thanks!
-
Some more stuff:
On very rare occation nested triggers are usefull and even in that case I still try to go for other non-recursive method of accomplishing the same thing! Therefore yes I would listed on the BAD Practices if you will
2. All constraints are checked before a trigger can fire that's what Dylan meant! if one of your columns is defined as NOT Null and you ommit the parameter or send a NULL value the statement will error out and the trigger wont be Fired (your Trigger Code Won't Run)
3. Triggers Fire on STATEMETS not on Columns! So when you modify any column on a table where you have a trigger firing action defined provided that all constraints are met
4. About Iserted and Deleted Tables
Read the Enforcing Bussiness Rules with Triggers in BOL (the whole chapter)
5. MY #1 BOOK recomemdation always is Books online. Only and only when you couldn't find what you need there you should look for more advaced books or explanation. In my opinion it does a pretty good job!!
* Noel -
Thanks guys and Noel!
I think i'm finally starting to understand the BOL. You can do a search and then click the contents tab and see all the related info in that chapter from that expansion tree.
Regarding this statement: "A table can have multiple AFTER triggers of a given type provided they have different names; each trigger can perform numerous functions. However, each trigger can apply to only one table, although a single trigger can apply to any subset of three user actions (UPDATE, INSERT, and DELETE)."
So basically everywhere i have a LastUpdated column i will have to cut and paste the same trigger for that table. I was wondering that. I meantion this because i was wondering if there was a better way to do this than cut and paste. Cut and paste just seems a little redundant!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply