T-SQL筆記32_Trigger觸發_獲取異動前後欄位值

- 6月 15, 2022

一個實驗範例

CREATE TABLE TestColumns
(
   Column_1 INT PRIMARY KEY,
   Column_2 INT,
   Column_3 INT,
   Column_4 INT
)
GO

INSERT INTO dbo.TestColumns ( Column_1, Column_2, Column_3, Column_4 )
VALUES ( 0, 0, 0, 0 )
GO

CREATE TRIGGER TR_TestColumns ON dbo.TestColumns
AFTER UPDATE 
AS
IF UPDATE(Column_1)
BEGIN
   ;THROW 51000, 'You can''t update the primary key', 1;  
END
 
IF UPDATE(Column_2)
BEGIN
   DECLARE @Col2_Before int;
   DECLARE @Col2_After int;
   select @Col2_Before = Column_2 from deleted 
   select @Col2_After = Column_2 from inserted
   PRINT 'Column_2 was updated'
   PRINT 'before column val:' + CAST(@Col2_Before as varchar(10))
   PRINT 'alter column val:' + CAST(@Col2_After as varchar(10))
END
 
IF UPDATE(Column_3)
BEGIN
   PRINT 'Column_3 was updated'
END
 
IF UPDATE(Column_4)
BEGIN
   PRINT 'Column_4 was updated'
END


GO

一個測試更新語法

1
2
3

UPDATE dbo.TestColumns SET Column_2 = 2
WHERE Column_1 = 0 
GO

結果輸出

Trigger 還有區分為發生後的(After/FOR) 跟發生前的(Instead Of)

此外也可針對一整個DB在進行trigger設計撰寫

以下為測試用的腳本

-- Database in-use

USE [SampleDB]

-- Sample Table to be Used for Functions

CREATE TABLE Employee
(EmpId INT PRIMARY KEY,
Name VARCHAR(50),
Salary INT,
Address VARCHAR(100))

SELECT * FROM Employee

INSERT INTO Employee VALUES(101, 'King Kocchar', 12000, 'Delhi')
INSERT INTO Employee VALUES(102, 'John Smith', 8000, 'Mumbai')
INSERT INTO Employee VALUES(103, 'Sarah Bowling', 32000, 'Delhi')
INSERT INTO Employee VALUES(104, 'James Bond', 22000, 'Chennai')

-- Employee Audit Table

CREATE TABLE EmployeeAudit
(EmpAuditId INT IDENTITY(1,1),
EmpId INT,
Name VARCHAR(50),
Salary INT,
AuditAction VARCHAR(100),
AuditTimeStamp DATETIME)

-- After Insert Trigger

CREATE TRIGGER trgAfterInsert ON Employee
FOR INSERT
AS 
DECLARE 
@empid INT, 
@empname VARCHAR(50), 
@empsal INT, 
@auditaction VARCHAR(100)
SELECT @empid = i.EmpId from inserted i;
SELECT @empname = i.Name from inserted i;
SELECT @empsal = i.Salary from inserted i;
SET @auditaction ='INSERT'
INSERT INTO EmployeeAudit(EmpId, Name, Salary,AuditAction, AuditTimeStamp)
VALUES(@empid, @empname,@empsal, @auditaction, GETDATE())
PRINT 'After Trigger Fired Successfully.'

SELECT * FROM Employee
SELECT * FROM EmployeeAudit

INSERT INTO Employee VALUES(105, 'Bhawna Gunwani', 10000, 'Delhi')

-- After Update Trigger

ALTER TRIGGER trgAfterUpdate ON Employee
FOR UPDATE
AS
DECLARE
@empid INT, 
@empname VARCHAR(50), 
@empsal INT, 
@auditaction VARCHAR(100)
SELECT @empid = i.EmpId from deleted i;
SELECT @empname = i.Name from deleted i;
SELECT @empsal = i.Salary from deleted i;
SET @auditaction ='UPDATE'
IF UPDATE(Salary)
	INSERT INTO EmployeeAudit (EmpId, Name, Salary,AuditAction, AuditTimeStamp)
	VALUES(@empid, @empname,@empsal, @auditaction, GETDATE())
PRINT 'Update Trigger Fired Successfully.'

SELECT * FROM Employee
SELECT * FROM EmployeeAudit

UPDATE Employee
SET Salary = 40000, Address = 'Hyderabad'
WHERE EmpId = 105

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

-- After Delete Trigger

CREATE TRIGGER trgAfterDelete ON Employee
FOR DELETE
AS
DECLARE
@empid INT, 
@empname VARCHAR(50), 
@empsal INT, 
@auditaction VARCHAR(100)
SELECT @empid = i.EmpId from deleted i;
SELECT @empname = i.Name from deleted i;
SELECT @empsal = i.Salary from deleted i;
SET @auditaction ='DELETE'
INSERT INTO EmployeeAudit (EmpId, Name, Salary,AuditAction, AuditTimeStamp)
	VALUES(@empid, @empname,@empsal, @auditaction, GETDATE())
PRINT 'Delete Trigger Fired Successfully.'

SELECT * FROM Employee
SELECT * FROM EmployeeAudit

DELETE FROM Employee
WHERE EmpId = 105


-- Instead Of Insert Trigger

CREATE TRIGGER trgInsteadOfInsert ON Employee
INSTEAD OF INSERT
AS 
DECLARE 
@empid INT, 
@empname VARCHAR(50), 
@empsal INT, 
@auditaction VARCHAR(100)
SELECT @empid = i.EmpId from inserted i;
SELECT @empname = i.Name from inserted i;
SELECT @empsal = i.Salary from inserted i;
SET @auditaction ='INSERT'
BEGIN
	BEGIN TRANSACTION
	SET NOCOUNT ON
	IF (@empsal >= 10000)
		BEGIN
			RAISERROR('Cannot insert where salary > 10000', 16, 1)
			ROLLBACK;
		END
	ELSE
		BEGIN
			INSERT INTO EmployeeAudit(EmpId, Name, Salary,AuditAction, AuditTimeStamp)
			VALUES(@empid, @empname,@empsal, @auditaction, GETDATE())
			COMMIT;
			PRINT 'After Trigger Fired Successfully.'
		END
END


INSERT INTO Employee VALUES(105, 'Bhawna Gunwani', 8000, 'Delhi')

SELECT * FROM Employee
SELECT * FROM EmployeeAudit

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

-- Logon Trigger

SELECT * FROM sys.dm_exec_sessions

SELECT is_user_process, original_login_name, *
FROM sys.dm_exec_sessions 
ORDER BY login_name

SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

CREATE TRIGGER trCheckLogin
ON ALL SERVER
FOR LOGON
AS
BEGIN
	DECLARE @login VARCHAR(100)
	SET @login = ORIGINAL_LOGIN()

	IF
	(SELECT COUNT(*) 
	FROM sys.dm_exec_sessions 
	WHERE is_user_process = 1 AND original_login_name = @login) > 3
	BEGIN
		PRINT 'More than three connections are not allowed - Connection By ' + @login;
		ROLLBACK;
	END
END