A Simple Tutorial for DML Triggers

Question

A Simple Tutorial for DML Triggers

Vincent92

Old Hand

Points: 303
More actions
February 15, 2023 at 12:00 am

#4078282

Comments posted to this topic are about the item A Simple Tutorial for DML Triggers

Viewing 7 posts - 1 through 6 (of 6 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

I hate to say it, but this has a terrible example. Your first trigger, Trigger_Insert makes the assumption that the INSERT only ever contains one row, which is simply not the case. That trigger won't work correctly if 2 or more rows are INSERTed. Assigning values to variables from the inserted/deleted pseudo-table is a common, but fatal mistake.

Take these INSERT statements:

INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (1, 'John', 37);
GO
--Fails
INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (2, 'Jane', 137);
GO
--Might fails, might work
INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (3, 'Greg', 131),
       (4, 'Hannah', 29);
GO
--Might fails, might work
INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (5, 'Sam', 18),
       (6, 'Samantha', 142);

For the above, in a fiddle, the row for Samantha, who is an age of 142, the insert works: db<>fiddle

Also, not to mention, I'm really not a fan of just doing a ROLLBACK in the trigger. This produces a somewhat vage error:

Msg 3609, Level 16, State 1, Line 57
The transaction ended in the trigger. The batch has been aborted.

This doesn't tell the end user what the problem was, or anything informative. You would be much better off THROWing an error, with a meaningful message. There's no need for the ROLLBACK in the TRIGGER as the outer scope should already be handling the transaction.

For your trigger Trigger_Insert, though a CHECK CONSTRAINT would be a much better idea (ignoring that age is a bad value to store over date of birth, as this is an example), I would write the definition as something like this:

CREATE TRIGGER [dbo].[Trigger_Insert] ON [dbo].[Person]
AFTER INSERT AS
BEGIN

    SET NOCOUNT ON;
    --If a single row with a value of 130 or more exists throw as error, otherwise insert as normal.
    IF EXISTS(SELECT 1 FROM Inserted WHERE Age >= 130)
        THROW 56789, N'Insert on table ''dbo.Person'' failed in the trigger ''dbo.Trigger_Insert''. The age of a person cannot be greater than or equal to 130.', 16;
    ELSE
        INSERT INTO PersonLog (PersonID,
                               Name,
                               Age,
                               AddDate)
        SELECT ID,
               Name,
               Age,
               GETDATE()
        FROM inserted;
END;
GO

This causes the 2 INSERT statements with ages greater than 130 to error as well: db<>fiddle

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

I appreciate you taking the time to provide examples to explain triggers.

You did mention "Try to avoid time-consuming operations in the trigger" yet you make use of creating temporary tables as in

SELECT PersonID,AddDate into #pel from PersonLog

There is no telling how huge personLog can be (it is a log table) and to create it for every call that creates an update is not something I view as favorable. Imagine a high traffic website that is constantly updating records, this would not pass a code review.

I would alter your advise to state that you have to avoid time consuming operations in a trigger. If there is more needed it should be part of a procedure (stored procedure call).

----------------------------------------------------

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

I too was wondering why the temp table. I've had to create several AFTER INSERT/UPDATE triggers and never even considered adding a temp table to the process. I Just use teh INSERTED & DELETED tables and then what ever tables being inserted or updated. Even have had to do a few INSTEAD OF Triggers. After reading this (but not the replies) I was wondering if maybe I was doing it wrong.

Kindest Regards,

Just say No to Facebook!

Vincent92 Old Hand Points: 303 More actions · Answer 4

Vincent92

Old Hand

Points: 303

February 27, 2023 at 8:08 am

#4154490

Thank you for your suggestions and replies

Vincent92 Old Hand Points: 303 More actions · Answer 5

I use a temp table for multi-value updating by trigger

Thom A SSC Guru Points: 99130 More actions · Answer 6

Vincent92 wrote:

I use a temp table for multi-value updating by trigger

What is your reason for doing that? What functionality or performance benefit does the temporary provide over not using one (and going straight to the inserted/deleted object)?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk