sqlserver 触发器 insert/update/delete

新建一张数据表用于测试

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[trigger_user](
	[name] [nchar](10) NULL,
	[age] [nchar](10) NULL
) ON [PRIMARY]

GO

一、insert 触发器

示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。

--insert触发器
if (object_id('trigger_user_insert') is not null)
    drop trigger trigger_user_insert
go
	create trigger trigger_user_insert
	on trigger_user --表名
	    for insert --插入后触发
		--instead of insert --插入前触发,使用插入前触发时,不执行默认插入
	as
		--开始执行逻辑
	
	    declare @name int, @age varchar(20), @temp int;
	    select @name = name, @age = age from inserted -- inserted 存放了当前插入的值
		select @name,@age
go

执行insert 语句后,触发触发器,输出name和age

二、update触发器

	--update触发器
if (object_id('trigger_user_update') is not null)
    drop trigger trigger_user_update
go
create trigger trigger_user_update
on trigger_user --表名
    for update
	-- instead of update 更新前触发
as
    --开始执行逻辑
	select * from trigger_user
    declare @name int, @age varchar(20), @temp int;
    select @name = name, @age = age from inserted -- inserted 存放了当前更新传入的值
	select @name,@age
go

三、delete触发器

--delete触发器
if (object_id('trigger_user_delete') is not null)
    drop trigger trigger_user_delete
go
create trigger trigger_user_delete
on trigger_user --表名
    for delete
	-- instead of update 
as
    --开始执行逻辑
	select * from trigger_user
    declare @name int, @age varchar(20), @temp int;
    select @name = name, @age = age from deleted -- deleted 存放了当前删除的条件值
	select @name,@age
go

最后将测试表和测试触发器删除

	if (object_id('trigger_user_insert') is not null)
    drop trigger trigger_user_insert
	if (object_id('trigger_user_update') is not null)
	    drop trigger trigger_user_update
	if (object_id('trigger_user_delete') is not null)
	    drop trigger trigger_user_delete
	if (object_id('trigger_user') is not null)
	    drop table trigger_user