--------------------------------------------------------------------
-- Appendix F - Dynamically Creating Triggers for Cascade Actions --
--------------------------------------------------------------------
-- Listing F-1: Creation Script for the sp_RICascadeDelete Stored Procedure
CREATE PROC sp_RICascadeDelete
@prmtbl sysname, -- Primary (referenced) table
@sectbl sysname, -- Secondary (referencing) table
@prmcol sysname, -- Primary (referenced) column
@seccol sysname -- Secondary (referencing) column
AS
DECLARE @ENTER AS char(1)
DECLARE @cmd AS varchar(8000)
SET @ENTER = CHAR(10)
IF @prmtbl != @sectbl
BEGIN
SET @cmd =
'CREATE TRIGGER trg_d_' + @prmtbl + '_on_delete_cascade ON ' +
@prmtbl + ' FOR DELETE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'IF @@rowcount = 0' + @ENTER +
' RETURN' + @ENTER +
@ENTER +
'DELETE FROM ' + @sectbl + @ENTER +
'FROM ' + @ENTER +
' ' + @sectbl + ' AS S' + @ENTER +
' JOIN' + @ENTER +
' deleted AS D ON S.' + @seccol + ' = D.' + @prmcol + @ENTER
END
ELSE
BEGIN
SET @cmd =
'CREATE TRIGGER trg_d_' + @prmtbl + '_on_delete_cascade ON ' +
@prmtbl + ' FOR DELETE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'IF EXISTS(SELECT *' + @ENTER +
' FROM ' + @ENTER +
' ' + @prmtbl + ' AS S' + @ENTER +
' JOIN' + @ENTER +
' deleted AS D ON S.' + @seccol + ' = D.' + @prmcol +')' + @ENTER +
' DELETE FROM ' + @sectbl + @ENTER +
' FROM' + @ENTER +
' ' + @sectbl + ' AS S' + @ENTER +
' JOIN' + @ENTER +
' deleted AS D ON S.' + @seccol + ' = D.' + @prmcol + @ENTER
PRINT 'Recursive triggers should be turned on.' + @ENTER +
'Use EXEC sp_dboption ' + DB_NAME() +
', ''recursive triggers'', ''true'''+ @ENTER
END
EXECUTE(@cmd)
GO
-- Listing F-2: Creation Script for the sp_RIPreventDelete Stored Procedure
CREATE PROC sp_RIPreventDelete
@prmtbl sysname, -- Primary (referenced) table
@sectbl sysname, -- Secondary (referencing) table
@prmcol sysname, -- Primary (referenced) column
@seccol sysname -- Secondary (referencing) column
AS
DECLARE @ENTER AS char(1)
DECLARE @cmd AS varchar(8000)
SET @ENTER = CHAR(10)
SET @cmd =
'CREATE TRIGGER trg_d_' + @prmtbl + '_prevent_delete ON ' +
@prmtbl + ' FOR DELETE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'IF @@rowcount = 0' + @ENTER +
' RETURN' + @ENTER +
@ENTER +
'IF EXISTS(SELECT *' + @ENTER +
' FROM ' + @ENTER +
' ' + @sectbl + ' AS S' + @ENTER +
' JOIN' + @ENTER +
' deleted AS D ON S.' + @seccol + ' = D.' + @prmcol + ')' + @ENTER +
'BEGIN' + @ENTER +
' RAISERROR(''You are trying to delete rows from ' + @prmtbl +
' that have related rows in ' + @sectbl +
'.TRANSACTION rolled back.'', 10, 1)' + @ENTER +
' ROLLBACK TRANSACTION' + @ENTER +
'END'
EXECUTE(@cmd)
GO
-- Listing F-3: Creation Script for the sp_RICascadeUpdate Stored Procedure
CREATE PROC sp_RICascadeUpdate
@prmtbl sysname, -- Primary (referenced) table
@sectbl sysname, -- Secondary (referencing) table
@prmcol sysname, -- Primary (referenced) column
@seccol sysname -- Secondary (referencing) column
AS
DECLARE @ENTER AS char(1)
DECLARE @cmd AS varchar(8000)
SET @ENTER = CHAR(10)
SET @cmd =
'CREATE TRIGGER trg_u_' + @prmtbl + '_on_update_cascade ON ' +
@prmtbl + ' FOR UPDATE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'DECLARE @numrows AS int' + @ENTER +
'SET @numrows = @@rowcount' + @ENTER +
'IF UPDATE(' + @prmcol + ')' + @ENTER +
' IF @numrows = 1' + @ENTER +
' UPDATE ' + @sectbl + @ENTER +
' SET ' + @seccol + ' = (SELECT ' + @prmcol + ' FROM inserted)' + @ENTER +
' FROM ' + @ENTER +
' ' + @sectbl + ' AS S' + @ENTER +
' JOIN' + @ENTER +
' deleted AS D ON S.' + @seccol + ' = D.' + @prmcol + @ENTER +
' ELSE IF @numrows > 1' + @ENTER +
' BEGIN' + @ENTER +
' RAISERROR(''Updates to more than one row in ' + @prmtbl +
' are not allowed. TRANSACTION rolled back.'', 10, 1)' + @ENTER +
' ROLLBACK TRANSACTION' + @ENTER +
' END' + @ENTER
EXEC(@cmd)
PRINT 'Only single row updates are allowed to ' + @prmtbl + '.' + @ENTER +
'To support multi-row updates add a surrogate key and alter the trigger:' + @ENTER
PRINT 'ALTER TABLE ' + @prmtbl + @ENTER +
' ADD surrogate_key int NOT NULL IDENTITY(1,1)' + @ENTER +
' CONSTRAINT UNQ_' + @prmtbl + '_surrogate_key UNIQUE' + @ENTER +
'GO' + @ENTER
PRINT 'ALTER TRIGGER trg_u_' + @prmtbl + '_on_update_cascade ON ' +
@prmtbl + ' FOR UPDATE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'DECLARE @numrows AS int' + @ENTER +
'SET @numrows = @@rowcount' + @ENTER +
'IF UPDATE(surrogate_key)' + @ENTER +
'BEGIN' + @ENTER +
' RAISERROR(''Updates to surrogate_key are not allowed. TRANSACTION rolled back.'', 10, 1)' + @ENTER +
' ROLLBACK TRANSACTION' + @ENTER +
'END' + @ENTER +
'ELSE' + @ENTER +
' IF UPDATE(' + @prmcol + ') AND @numrows > 0' + @ENTER +
' UPDATE ' + @sectbl + @ENTER +
' SET ' + @seccol + ' = I.' + @prmcol + @ENTER +
' FROM ' + @ENTER +
' ' + @sectbl + ' AS S' + @ENTER +
' JOIN' + @ENTER +
' deleted AS D ON S.' + @seccol + ' = D.' + @prmcol + @ENTER +
' JOIN' + @ENTER +
' inserted AS I ON D.surrogate_key = I.surrogate_key' + @ENTER +
'GO' + @ENTER
GO
CREATE PROC sp_RIPreventUpdate
@prmtbl sysname, -- Primary (referenced) table
@sectbl sysname, -- Secondary (referencing) table
@prmcol sysname, -- Primary (referenced) column
@seccol sysname -- Secondary (referencing) column
AS
DECLARE @ENTER AS char(1)
DECLARE @cmd AS varchar(8000)
SET @ENTER = CHAR(10)
SET @cmd =
'CREATE TRIGGER trg_d_' + @prmtbl + '_prevent_update ON ' +
@prmtbl + ' FOR UPDATE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'IF @@rowcount = 0' + @ENTER +
' RETURN' + @ENTER +
@ENTER +
'IF EXISTS(SELECT *' + @ENTER +
' FROM' + @ENTER +
' ' + @sectbl + ' AS S' + @ENTER +
' JOIN' + @ENTER +
' deleted AS D ON S.' + @seccol + ' = D.' + @prmcol + ')' + @ENTER +
'BEGIN' + @ENTER +
' RAISERROR(''You are trying to update rows in ' + @prmtbl +
' that have related rows in ' + @sectbl +
'.TRANSACTION rolled back.'', 10, 1)' + @ENTER +
' ROLLBACK TRANSACTION' + @ENTER +
'END'
EXECUTE(@cmd)
GO
-- Listing F-5: Creation Script for the sp_RIPreventInsUpd Stored Procedure
CREATE PROC sp_RIPreventInsUpd
@prmtbl sysname, -- Primary (referenced) table
@sectbl sysname, -- Secondary (referencing) table
@prmcol sysname, -- Primary (referenced) column
@seccol sysname -- Secondary (referencing) column
AS
DECLARE @ENTER AS char(1)
DECLARE @cmd AS varchar(8000)
SET @ENTER = CHAR(10)
IF @prmtbl != @sectbl
BEGIN
SET @cmd =
'CREATE TRIGGER trg_iu_' + @sectbl + '_prevent_insupd ON ' +
@sectbl + ' FOR INSERT, UPDATE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'DECLARE @numrows int' + @ENTER +
'SET @numrows = @@rowcount' + @ENTER +
@ENTER +
'IF UPDATE(' + @seccol + ') AND @numrows > 0' + @ENTER +
' IF @numrows <> (SELECT' + @ENTER +
' COUNT(*)' + @ENTER +
' FROM' + @ENTER +
' ' + @prmtbl + ' AS P' + @ENTER +
' JOIN' + @ENTER +
' inserted AS I ON P.' + @prmcol + ' = I.' + @seccol + ')' + @ENTER +
' BEGIN' + @ENTER +
' RAISERROR(''Result rows in ' + @sectbl + ' are orphaned. TRANSACTION rolled back.'', 10, 1)' + @ENTER +
' ROLLBACK TRANSACTION' + @ENTER +
' END' + @ENTER
END
ELSE
BEGIN
SET @cmd =
'CREATE TRIGGER trg_iu_' + @sectbl + '_prevent_insupd ON ' +
@sectbl + ' FOR INSERT, UPDATE' + @ENTER +
'AS' + @ENTER +
@ENTER +
'IF @@rowcount > 0 AND UPDATE(' + @seccol + ')' + @ENTER +
'BEGIN' + @ENTER +
' DECLARE @numrows int' + @ENTER +
@ENTER +
' SELECT' + @ENTER +
' @numrows = COUNT(*)' + @ENTER +
' FROM' + @ENTER +
' inserted' + @ENTER +
' WHERE' + @ENTER +
' ' + @seccol + ' IS NOT NULL' + @ENTER +
@ENTER +
' IF @numrows <> (SELECT' + @ENTER +
' COUNT(*)' + @ENTER +
' FROM' + @ENTER +
' ' + @prmtbl + ' AS P' + @ENTER +
' JOIN' + @ENTER +
' inserted AS I ON P.' + @prmcol + ' = I.' + @seccol + ')' + @ENTER +
' BEGIN' + @ENTER +
' RAISERROR(''Result rows in ' + @sectbl + ' are orphaned. TRANSACTION rolled back.'', 10, 1)' + @ENTER +
' ROLLBACK TRANSACTION' + @ENTER +
' END' + @ENTER +
'END' + @ENTER
END
EXECUTE(@cmd)
GO
-- Listing F-6: Creation Script for the sp_CreateRelationship Stored Procedure
CREATE PROC sp_CreateRelationship
@prmtbl sysname, -- Primary (referenced) table
@sectbl sysname, -- Secondary (referencing) table
@prmcol sysname, -- Primary (referenced) column
@seccol sysname, -- Secondary (referencing) column,
@deletecascade bit = 1, -- determines whether to support cascade delete
@updatecascade bit = 1 -- determines whether to support cascade update
AS
DECLARE @ENTER AS char(1)
DECLARE @cmd AS varchar(8000)
SET @ENTER = CHAR(10)
IF @deletecascade = 1
BEGIN
PRINT 'Creating delete cascade trigger on ' + @prmtbl + '...' + @ENTER
EXEC sp_RICascadeDelete
@prmtbl = @prmtbl,
@sectbl = @sectbl,
@prmcol = @prmcol,
@seccol = @seccol
END
ELSE
BEGIN
PRINT 'Creating prevent delete trigger on ' + @prmtbl + '...' + @ENTER
EXEC sp_RIPreventDelete
@prmtbl = @prmtbl,
@sectbl = @sectbl,
@prmcol = @prmcol,
@seccol = @seccol
END
IF @updatecascade = 1
BEGIN
PRINT 'Creating update cascade trigger on ' + @prmtbl + '...' + @ENTER
EXEC sp_RICascadeUpdate
@prmtbl = @prmtbl,
@sectbl = @sectbl,
@prmcol = @prmcol,
@seccol = @seccol
END
ELSE
BEGIN
PRINT 'Creating prevent update trigger on ' + @prmtbl + '...' + @ENTER
EXEC sp_RIPreventUpdate
@prmtbl = @prmtbl,
@sectbl = @sectbl,
@prmcol = @prmcol,
@seccol = @seccol
END
PRINT 'Creating prevent insert / update trigger on ' + @sectbl + '...' + @ENTER
EXEC sp_RIPreventInsUpd
@prmtbl = @prmtbl,
@sectbl = @sectbl,
@prmcol = @prmcol,
@seccol = @seccol
GO
-- Listing F-7: Using the sp_CreateRelationship Stored Procedure to Create Cascading Triggers in a Two-Tables Relationship Scenario
EXEC sp_CreateRelationship
@prmtbl = Orders,
@sectbl = OrderDetails,
@prmcol = orderid,
@seccol = orderid,
@deletecascade = 1,
@updatecascade = 1
-- Listing F-8: Output From the sp_CreateRelationship Stored Procedure in a Two-Tables Relationship Scenario
Creating delete cascade trigger on Orders...
Creating update cascade trigger on Orders...
Only single row updates are allowed to Orders.
To support multi-row updates add a surrogate key and alter the trigger:
ALTER TABLE Orders
ADD surrogate_key int NOT NULL IDENTITY(1,1)
CONSTRAINT UNQ_Orders_surrogate_key UNIQUE
GO
ALTER TRIGGER trg_u_Orders_on_update_cascade ON Orders FOR UPDATE
AS
DECLARE @numrows AS int
SET @numrows = @@rowcount
IF UPDATE(surrogate_key)
BEGIN
RAISERROR('Updates to surrogate_key are not allowed. TRANSACTION rolled back.', 10, 1)
ROLLBACK TRANSACTION
END
ELSE
IF UPDATE(orderid) AND @numrows > 0
UPDATE OrderDetails
SET orderid = I.orderid
FROM
OrderDetails AS S
JOIN
deleted AS D ON S.orderid = D.orderid
JOIN
inserted AS I ON D.surrogate_key = I.surrogate_key
GO
Creating prevent insert / update trigger on OrderDetails...
-- Listing F-9: Using the sp_CreateRelationship Stored Procedure to Create Cascading Triggers in a Single-Tables Relationship Scenario
EXEC sp_CreateRelationship
@prmtbl = Employees,
@sectbl = Employees,
@prmcol = empid,
@seccol = mgrid,
@deletecascade = 1,
@updatecascade = 1
-- Listing F-10: Output From the sp_CreateRelationship Stored Procedure in a Single-Tables Relationship Scenario
Creating delete cascade trigger on Employees...
Recursive triggers should be turned on.
Use EXEC sp_dboption testdb, 'recursive triggers', 'true'
Creating update cascade trigger on Employees...
Only single row updates are allowed to Employees.
To support multi-row updates add a surrogate key and alter the trigger:
ALTER TABLE Employees
ADD surrogate_key int NOT NULL IDENTITY(1,1)
CONSTRAINT UNQ_Employees_surrogate_key UNIQUE
GO
ALTER TRIGGER trg_u_Employees_on_update_cascade ON Employees FOR UPDATE
AS
DECLARE @numrows AS int
SET @numrows = @@rowcount
IF UPDATE(surrogate_key)
BEGIN
RAISERROR('Updates to surrogate_key are not allowed. TRANSACTION rolled back.', 10, 1)
ROLLBACK TRANSACTION
END
ELSE
IF UPDATE(empid) AND @numrows > 0
UPDATE Employees
SET mgrid = I.empid
FROM
Employees AS S
JOIN
deleted AS D ON S.mgrid = D.empid
JOIN
inserted AS I ON D.surrogate_key = I.surrogate_key
GO
Creating prevent insert / update trigger on Employees...
