什么是触发器?SQL中的哪几种操作会触发触发器?

发布网友发布时间：2022-04-08 11:06

我来回答

共4个回答

懂视网时间：2022-04-08 15:27

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

标签：

热心网友时间：2022-04-08 12:35

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

热心网友时间：2022-04-08 13:53

触发器是用户定义在关系表上的一类由事件触发的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。触发器类似于约束，但是比约束灵活，具有更强大的数据控制能力。

热心网友时间：2022-04-08 15:28

前触发你对表数据进行操作之前做的事情
后触发你对表数据进行操作之后做的事情
对表数据的操作无非增删改（查询没有触发的）