您现在的位置是：网站首页> 编程资料编程资料
sql 触发器使用例子_MsSql_
2023-05-26
397人已围观
简介 sql 触发器使用例子_MsSql_
inserted,deleted是在触发器中使用的两个临时表，当执行insert操作时，在inserted中存储着当前插入的记录，在执行delete操作时，在deleted中存储着当前删除的记录,当执行update时，在inserted中存储着修改后的记录，在deleted中存储着修改前的记录。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [dnt_user_add]
ON [dbo].[dnt_users]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [dnt_userfields](uid)
select uid from inserted
END
删除：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dnt_users_del]
ON [dnt_users]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
delete [dnt_userfields] from deleted where [dnt_userfields].uid=deleted.uid
END
GO
修改：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dnt_users_up]
ON [dnt_users]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [dnt_userfields] Set icq = inserted.uid from inserted where [dnt_userfields].uid = inserted.uid
END
GO
复制代码 代码如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [dnt_user_add]
ON [dbo].[dnt_users]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [dnt_userfields](uid)
select uid from inserted
END
删除：
复制代码 代码如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dnt_users_del]
ON [dnt_users]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
delete [dnt_userfields] from deleted where [dnt_userfields].uid=deleted.uid
END
GO
修改：
复制代码 代码如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dnt_users_up]
ON [dnt_users]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [dnt_userfields] Set icq = inserted.uid from inserted where [dnt_userfields].uid = inserted.uid
END
GO
下一篇：sql 刷新视图_MsSql_
相关内容
- 数据库 日期加减处理_MsSql_
- SQL Server 数据库优化_MsSql_
- sql 查询慢的原因分析_MsSql_
- 模糊查询的通用存储过程_MsSql_
- SQL Server 中查看SQL句子执行所用的时间_MsSql_
- MySql 安装时的1045错误_MsSql_
- 数据库Left join , Right Join, Inner Join 的相关内容，非常实用_MsSql_
- SQL Server 排序函数 ROW_NUMBER和RANK 用法总结_MsSql_
- sql server 常用的几个数据类型_MsSql_
- SQLServer EVENTDATA()函数来获取DDL 触发器信息_MsSql_
