Example 14.
1
USE sample;
GO
CREATE TABLE audit_budget
(project_no CHAR(4) NULL,
user_name CHAR(16) NULL,
date DATETIME NULL,
budget_old FLOAT NULL,
budget_new FLOAT NULL);
GO
CREATE TRIGGER modify_budget
ON project AFTER UPDATE
AS IF UPDATE(budget)
BEGIN
DECLARE @budget_old FLOAT
DECLARE @budget_new FLOAT
DECLARE @project_number CHAR(4)
SELECT @budget_old = (SELECT budget FROM deleted)
SELECT @budget_new = (SELECT budget FROM inserted)
SELECT @project_number = (SELECT project_no FROM deleted)
INSERT INTO audit_budget VALUES
(@project_number,USER_NAME(),GETDATE(),@budget_old, @budget_new)
END
Example 14.2
-- The trigger total_budget is an example of using a trigger to implement a business rule
USE sample;
GO
CREATE TRIGGER total_budget
ON project AFTER UPDATE
AS IF UPDATE (budget)
BEGIN
DECLARE @sum_old1 FLOAT
DECLARE @sum_old2 FLOAT
DECLARE @sum_new FLOAT
SELECT @sum_new = (SELECT SUM(budget) FROM inserted)
SELECT @sum_old1 = (SELECT SUM(p.budget)
FROM project p WHERE p.project_no
NOT IN (SELECT d.project_no FROM deleted d))
SELECT @sum_old2 = (SELECT SUM(budget) FROM deleted)
IF @sum_new > (@sum_old1 + @sum_old2) *1.5
BEGIN
PRINT 'No modification of budgets'
ROLLBACK TRANSACTION
END
ELSE
PRINT 'The modification of budgets executed'
END
Example 14.3
CREATE TRIGGER works_integrity
ON works_on AFTER UPDATE
AS IF UPDATE(emp_no)
BEGIN
IF (SELECT employee.emp_no
FROM employee, inserted
WHERE employee.emp_no = inserted.emp_no) IS NULL
BEGIN
ROLLBACK TRANSACTION
PRINT 'No insertion/modification of the row'
END
ELSE PRINT 'The row inserted/modified'
END
Example 14.4
USE sample;
GO
CREATE TRIGGER refint_workson2
ON employee AFTER UPDATE
AS IF UPDATE (emp_no)
BEGIN
IF (SELECT COUNT(*)
FROM WORKS_ON, deleted
WHERE works_on.emp_no = deleted.emp_no) > 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'No modification/deletion of the row'
END
ELSE PRINT 'The row is deleted/modified'
END
Example 14.5
EXEC sp_settriggerorder @triggername = 'modify_budget',
@order = 'first', @stmttype='update'
Example 14.6
USE sample;
GO
CREATE TRIGGER prevent_drop_synonyms
ON DATABASE FOR DROP_SYNONYM
AS PRINT 'You must disable "prevent_drop_synonyms" to drop any synonym'
Example 14.7
USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = 'login_test§$!',
CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 1
ROLLBACK;
END;
