触发器新增、修改、删除

最新推荐文章于 2024-11-26 11:47:28 发布

原创最新推荐文章于 2024-11-26 11:47:28 发布 · 5.8k 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了SQL中的触发器，用于在工程表[dbo].[ProjectItem]上记录数据的插入、更新和删除操作。当数据发生变化时，触发器会将变更前后的信息写入到[Log_ProjectItem]日志表中，包括所有字段及操作类型和时间戳。

/****** Object: Trigger [dbo].[trigger_Insert_ProjectItem] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--工程表插入触发器
ALTER trigger [dbo].[trigger_Insert_ProjectItem]
on [dbo].[ProjectItem]
after insert
as
insert into [Log_ProjectItem](GCID, XMID, GCMC, XMMC, JZMJ, GCFL1, GCFL2, GCFL3, SYXZ, SHDXNR, Address, JGLX, JCLX, DSCS,
DXCS, DSMJ, DXMJ, GCZJ, JHKGRQ, JHJGRQ, SGDW, SGDWZZZ, SGDWXMJL, XMJLJBZ, KCDW, KCDWZZZ,
XMKCFZR, XMKCFZRZCZGZH, SJDW, SJDWZZZ, XMJZSJFZR, XMJZSJFZRZCZGZH, XMJGSJFZR,
XMJGSJFZRZCZGZH, JLDW, JLDWZZZSH, JLS, JLSZCJGZH, QualifiedBook, NoticeAndContract, PersonnelList,
OtherFiles, JSDW, JSDWFDDB, JSDWXMFZR, JSDWFZRTEL, BBRQ, JDZSHYJ, XXJD, SHZTID, SHZT, IsTempSave,
ZJY, ZJYMC, SHR, SHRQ, CJRQ, XGRQ, CJR, CGR, BYID, SGDWTel, JSDWTel, JLDWTel, IsJunGong, IsGet, RegisterNo,
SerialNumber, Lon, Lat, ZJY2, ZJY2MC, ZJY3, ZJY3MC, ZJY4, ZJY4MC, EngType, LXR, LXR_Tel,[IsVaild],[CreateType],[LogCreateTime])

select *,'insert',getdate() from inserted

/****** Object: Trigger [dbo].[trigger_Update_ProjectItem] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--工程表修改触发器
ALTER trigger [dbo].[trigger_Update_ProjectItem]
on [dbo].[ProjectItem]
after update
as
insert into [Log_ProjectItem] (GCID, XMID, GCMC, XMMC, JZMJ, GCFL1, GCFL2, GCFL3, SYXZ, SHDXNR, Address, JGLX, JCLX, DSCS,
DXCS, DSMJ, DXMJ, GCZJ, JHKGRQ, JHJGRQ, SGDW, SGDWZZZ, SGDWXMJL, XMJLJBZ, KCDW, KCDWZZZ,
XMKCFZR, XMKCFZRZCZGZH, SJDW, SJDWZZZ, XMJZSJFZR, XMJZSJFZRZCZGZH, XMJGSJFZR,
XMJGSJFZRZCZGZH, JLDW, JLDWZZZSH, JLS, JLSZCJGZH, QualifiedBook, NoticeAndContract, PersonnelList,
OtherFiles, JSDW, JSDWFDDB, JSDWXMFZR, JSDWFZRTEL, BBRQ, JDZSHYJ, XXJD, SHZTID, SHZT, IsTempSave,
ZJY, ZJYMC, SHR, SHRQ, CJRQ, XGRQ, CJR, CGR, BYID, SGDWTel, JSDWTel, JLDWTel, IsJunGong, IsGet, RegisterNo,
SerialNumber, Lon, Lat, ZJY2, ZJY2MC, ZJY3, ZJY3MC, ZJY4, ZJY4MC, EngType, LXR, LXR_Tel,[IsVaild],[CreateType],[LogCreateTime])
select *,'update',getdate() from inserted

/****** Object: Trigger [dbo].[trigger_Delete_ProjectItem] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--工程表删除触发器
ALTER trigger [dbo].[trigger_Delete_ProjectItem]
on [dbo].[ProjectItem]
after delete
as
insert into [Log_ProjectItem](GCID, XMID, GCMC, XMMC, JZMJ, GCFL1, GCFL2, GCFL3, SYXZ, SHDXNR, Address, JGLX, JCLX, DSCS,
DXCS, DSMJ, DXMJ, GCZJ, JHKGRQ, JHJGRQ, SGDW, SGDWZZZ, SGDWXMJL, XMJLJBZ, KCDW, KCDWZZZ,
XMKCFZR, XMKCFZRZCZGZH, SJDW, SJDWZZZ, XMJZSJFZR, XMJZSJFZRZCZGZH, XMJGSJFZR,
XMJGSJFZRZCZGZH, JLDW, JLDWZZZSH, JLS, JLSZCJGZH, QualifiedBook, NoticeAndContract, PersonnelList,
OtherFiles, JSDW, JSDWFDDB, JSDWXMFZR, JSDWFZRTEL, BBRQ, JDZSHYJ, XXJD, SHZTID, SHZT, IsTempSave,
ZJY, ZJYMC, SHR, SHRQ, CJRQ, XGRQ, CJR, CGR, BYID, SGDWTel, JSDWTel, JLDWTel, IsJunGong, IsGet, RegisterNo,
SerialNumber, Lon, Lat, ZJY2, ZJY2MC, ZJY3, ZJY3MC, ZJY4, ZJY4MC, EngType, LXR, LXR_Tel,[IsVaild],[CreateType],[LogCreateTime])
select *,'delete',getdate() from deleted