SQL触发器Trigger

时间：2015-11-07 09:10:09 阅读：260 评论：0 收藏：0 [点我收藏+]

Create table Employee_Test(
 Emp_ID int Identity,
 Emp_name varchar(100),
 Emp_sal Decimal(10,2)
)

create table Employee_Test_Audit(
 Emp_ID int,
 Emp_name varchar(100),
 Emp_sal Decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime
)

After Inset Trigger

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);
	select @empid=i.Emp_ID from inserted i;	
	select @empname=i.Emp_Name from inserted i;	
	select @empsal=i.Emp_Sal from inserted i;	
	set @audit_action=‘Inserted Record -- After Insert Trigger.‘;
	insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());
	PRINT ‘AFTER INSERT trigger fired.‘
GO

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test] 
FOR UPDATE
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);
	select @empid=i.Emp_ID from inserted i;	
	select @empname=i.Emp_Name from inserted i;	
	select @empsal=i.Emp_Sal from inserted i;	
	
	if update(Emp_Name)
		set @audit_action=‘Updated Record -- After Update Trigger.‘;
	if update(Emp_Sal)
		set @audit_action=‘Updated Record -- After Update Trigger.‘;
	insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());
	PRINT ‘AFTER UPDATE Trigger fired.‘
GO

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test] 
AFTER DELETE
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);
	select @empid=d.Emp_ID from deleted d;	
	select @empname=d.Emp_Name from deleted d;	
	select @empsal=d.Emp_Sal from deleted d;	
	set @audit_action=‘Deleted -- After Delete Trigger.‘;
	insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());
	PRINT ‘AFTER DELETE TRIGGER fired.‘
GO

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
	declare @emp_id int;
	declare @emp_name varchar(100);
	declare @emp_sal int;
	
	select @emp_id=d.Emp_ID from deleted d;
	select @emp_name=d.Emp_Name from deleted d;
	select @emp_sal=d.Emp_Sal from deleted d;
	BEGIN
		if(@emp_sal>1200)
		begin
			RAISERROR(‘Cannot delete where salary > 1200‘,16,1);
			ROLLBACK;
		end
		else
		begin
			delete from Employee_Test where Emp_ID=@emp_id;
			COMMIT;
			insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
			values(@emp_id,@emp_name,@emp_sal,‘Deleted -- Instead Of Delete Trigger.‘,getdate());
			PRINT ‘Record Deleted -- Instead Of Delete Trigger.‘
		end
	END
GO

来自为知笔记(Wiz)

SQL触发器Trigger

原文：http://www.cnblogs.com/wupd2014/p/4944444.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)