Triggers and Cursors
And silly subtitles, oh my!
What Are Triggers?
Triggers are a special kind of stored procedure that is ran when certain database events - specifically, an INSERT, and UPDATE, or a DELETE - are ran.
Triggers can do anything any other stored procedure can do, but it’s important to note that they also have access to the the original data as well as the data that’s changing. (For instance, an UPDATE trigger has access to both the rows that are updated and the rows that are deleted, through special tables called INSERTED and DELETED.
You can make triggers by going into the [Triggers] folder under any table in your database. Right-click the folder and hit “Add Trigger” to pull up a very confusing looking template.
Example Trigger: INSERT
Once you understand what the template is asking for, it’s fairly easy to build them from the template.
(You can also just copy an example like this one).
This example adds a row to the log table when a new trigger is added.
Note the special INSERTED table: this can be used by a trigger to analyze the new data coming in.
-- =============================================
-- Author: Ryan Parish
-- Create date: 5/28/19
-- Description: This trigger adds a row
-- to the log file when tab_data gets a new row
-- =============================================
CREATE TRIGGER trg_data_insert --triggers should be named after the table
ON tab_data --table the trigger is on
AFTER INSERT --Type of trigger: usually update/delete/insert
AS
BEGIN
DECLARE
@ID int,
@NEW_DATA varchar(255)
SELECT
@NEW_DATA = data
FROM
INSERTED--special table: The new data that’s getting inserted
INSERT INTO tab_log
(comment)
VALUES
('Row added with "'+@NEW_DATA+'"')
END
Example Trigger: DELETE
The DELETE triggers have access to the DELETED table: this lets them see the values that are being deleted.
Note that this trigger happens AFTER the delete: if it weren’t for the DELETED table, there would be no way to access the old data.
-- =============================================
-- Author: Ryan Parish
-- Create date: 5/28/19
-- Description: This trigger adds a row
-- to the log file when deleting from tab_data
-- =============================================
CREATE TRIGGER trg_data_delete --triggers should be named after the table
ON tab_data --table the trigger is on
AFTER DELETE --Type of trigger: usually update/delete/insert
AS
BEGIN
DECLARE
@ID int
SELECT
@ID = DELETED.id
FROM
DELETED--special table: the data that’s getting deleted
INSERT INTO tab_log
(comment)
VALUES
('Row '+CAST(@ID AS varchar)+' deleted.')
END
Example Trigger: UPDATE
Update triggers have access to both the INSERTED and the DELETED tables, which means that we can use both the data before and after the update in our trigger if we want.
Note that I’m using FOR instead of AFTER on this trigger: that means that the trigger will happen first, and if the trigger hits an error the UPDATE will NOT happen!
ALTER TRIGGER [dbo].[trg_data_update] --Name triggers after the table
ON [dbo].[tab_data] --table the trigger is on
FOR UPDATE --Type of trigger: usually update/delete/insert
AS
BEGIN
DECLARE
@ID int,
@OLD_DATA varchar(255),
@NEW_DATA varchar(255)
SELECT
@ID=id,
@OLD_DATA = data
FROM
deleted--the data that's getting updated
SELECT
@NEW_DATA = data
FROM
inserted--the data that's getting updated
INSERT INTO
tab_log(comment)
VALUES
('Row '+CAST(@ID AS varchar)+' updated from '+@OLD_DATA+' to '+@NEW_DATA)
END
Dangers with Triggers
Because triggers activate whenever a specific event happens, it can cause performance issues. For instance, using a trigger that touches an external database - for instance, by sending an email - might take another second or two, which matters alot if it’s triggered often.
Also, triggers can do anything, even do updates and deletes - which may cause other triggers to fire! Poorly designed triggers can cause an infinite cascade of updates.
Cursors and Triggers
The examples I’ve shown you are simple: They will only work for a single row that is inserted, deleted, or updated!
If we try updating more than one row the trigger won’t work as expected.
To make the trigger work for more than one row, we need to modify it so that it can handle multiple rows updated at once.
--declare cursor
DECLARE simple_cursor CURSOR FOR
SELECT
data
FROM
INSERTED
OPEN simple_cursor
FETCH NEXT FROM simple_cursor
INTO @NEW_DATA
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
tab_log(comment)
VALUES
('Row added with "'+@NEW_DATA+'"')
--This FETCH NEXT gets the next row - otherwise this will run forever!
FETCH NEXT FROM simple_cursor
INTO @NEW_DATA
END