ceate trigger tr1 after insert,update,delete as begin insert into ... end
create trigger Trg_AAA on A after insert,update,delete as begin --更新 if exists(select 1 from deleted) and exists(select 1 from inserted) insert into C (col,...) select *,'UPD_DEL' from DELETED UNION ALL SELECT *,'UPD_INS' FROM INSERTED --删除 IF exists(select 1 from deleted) and NOT exists(select 1 from inserted) insert into C (col,...) select *,'DEL' from DELETED --添加 IF NOT exists(select 1 from deleted) AND exists(select 1 from inserted) insert into C (col,...) select *,'INS' from insertedend 表自己修改一下
网上找了一个 感觉很不错 ------------------- --Method 1: TRIGGER ------------------- --Base Table Definition IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2 varchar(1) NOT NULL ) GO INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b' INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a' GO --Create Audit Summary Table to hold Meta-Data IF OBJECT_ID('dbo.TableAuditSummary', 'U') IS NOT NULL DROP TABLE dbo.TableAuditSummary CREATE TABLE dbo.TableAuditSummary ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, TableName sysname NOT NULL, LastUpdate DATETIME NOT NULL, LastExport DATETIME NOT NULL ) GO INSERT dbo.TableAuditSummary (TableName, LastUpdate, LastExport) VALUES ('dbo.CheckSumTest', GETDATE(), GETDATE()) GO --Tables that need exporting SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport --Create Trigger on all Base Tables --This fires on any insert/update/delete and writes new LastUpdate column for the table set to Current Date and Time IF OBJECT_ID('dbo.trg_CheckSumTest_MaintainAuditSummary', 'TR') IS NOT NULL DROP TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary GO CREATE TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary ON dbo.CheckSumTest AFTER INSERT, UPDATE, DELETE AS BEGIN IF (object_id('dbo.CheckSumTest') IS NOT NULL) UPDATE dbo.TableAuditSummary SET LastUpdate=GETDATE() WHERE TableName='dbo.CheckSumTest' END GO --Make an Update UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1 UPDATE dbo.CheckSumTest SET vc1='a', vc2='b' WHERE id=2 INSERT dbo.CheckSumTest ( vc1, vc2 ) VALUES (
'c', 'c' )DELETE CheckSumTest WHERE id='1' --Check Meta-Data SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport --When we have Exported the data, we run the following to reset MetaData UPDATE dbo.TableAuditSummary SET LastExport=GETDATE() WHERE LastUpdate>LastExport
Create table A(ID int)go Create table A_Log(ID int,ChangeType nvarchar(20),Date datetime default getdate()) go create trigger tr_A on A after insert,update,delete as if not exists(select 1 from deleted) insert A_Log(ID,ChangeType) select ID,'新增' from inserted else if not exists(select 1 from inserted) insert A_Log(ID,ChangeType) select ID,'刪除' from deleted else insert A_Log(ID,ChangeType) select ID,'修改' from inserted i where not exists(select 1 from deleted where ID=i.ID)
触发器。 定义: 何为触发器?在SQL Server里面也就是对某一个表的一定的操作,触发某种条件,从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种:分别应用于Insert , Update , Delete 事件。(SQL Server 2000定义了新的触发器,这里不提) 我为什么要使用触发器?比如,这么两个表: Create Table Student( --学生表 StudentID int primary key, --学号 .... ) Create Table BorrowRecord( --学生借书记录表 BorrowRecord int identity(1,1), --流水号 StudentID int , --学号 BorrowDate datetime, --借出时间 ReturnDAte Datetime, --归还时间 ... ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 2.如果该学生已经毕业,我希望删除他的学号的同时,也删除它的借书记录。 等等。 这时候可以用到触发器。对于1,创建一个Update触发器: Create Trigger truStudent On Student for Update ------------------------------------------------------- --Name:truStudent --func:更新BorrowRecord 的StudentID,与Student同步。 --Use :None --User:System --Author: 懒虫 # SapphireStudio (www.chair3.com) --Date : 2003-4-16 --Memo : 临时写写的,给大家作个Sample。没有调试阿。 ------------------------------------------------------- As if Update(StudentID) begin Update BorrowRecord Set br.StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i Where br.StudentID=d.StudentID end
理解触发器里面的两个临时的表:Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。 一个Update 的过程可以看作为:生成新的记录到Inserted表,复制旧的记录到Deleted表,然后删除Student记录并写入新纪录。 对于2,创建一个Delete触发器 Create trigger trdStudent On Student for Delete ------------------------------------------------------- --Name:trdStudent --func:同时删除 BorrowRecord 的数据 --Use :None --User:System --Author: 懒虫 # SapphireStudio (www.chair3.com) --Date : 2003-4-16 --Memo : 临时写写的,给大家作个Sample。没有调试阿。 ------------------------------------------------------- As Delete BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID 从这两个例子我们可以看到了触发器的关键:A.2个临时的表;B.触发机制。 这里我们只讲解最简单的触发器。复杂的容后说明。 事实上,我不鼓励使用触发器。触发器的初始设计思想,已经被“级联”所替代。
create table A (id int,col int) insert A select 1,1 union all select 2,2 union all select 3,3 create table C(id int identity(1,1),UpdateType nvarchar(10))create trigger JiLu on A for insert,update,delete as begin Declare @updateType as nvarchar(10) set @updateType='' if exists(select 1 from inserted) begin if exists(select 1 from deleted) set @updateType='Update' else set @updateType='Insert' end else set @updateType='Delete'
if @updateType<>'' insert C(UpdateType) select @updateType endinsert A select 4,8select * from C--id UpdateType ------------- ---------- --1 Insert -- --(1 row(s) affected)update A set col=8 where id=2select * from C--id UpdateType ------------- ---------- --1 Insert --2 Update -- --(2 row(s) affected)delete A where id=3select * from C--id UpdateType ------------- ---------- --1 Insert --2 Update --3 Delete -- --(4 row(s) affected)
这里有个思路的问题,你的那个保存表里如何才能判断操作呢! 建议这个表的结构与原表相比加两个列,一个自动编号的列,可以显示顺序,另一个标志列,以保存是从inserted表还是从 deleted 表中来的,再加一个标志列,用于指示是什么操作,触发器中的写法前面都有,最简单的就是: insert into c select *,2 as flg from inserted insert intp c select *,2 as flg from deleted 这是更新触发器,插入触发器只有前一句,且标志 flg 为 1 删除触发器只有后一句,标志为 3.
after insert,update,delete
as
begin
insert into ...
end
create trigger Trg_AAA
on A
after insert,update,delete
as
begin
--更新
if exists(select 1 from deleted) and exists(select 1 from inserted)
insert into C (col,...)
select *,'UPD_DEL' from DELETED
UNION ALL
SELECT *,'UPD_INS' FROM INSERTED
--删除
IF exists(select 1 from deleted) and NOT exists(select 1 from inserted)
insert into C (col,...)
select *,'DEL' from DELETED
--添加
IF NOT exists(select 1 from deleted) AND exists(select 1 from inserted)
insert into C (col,...)
select *,'INS' from insertedend
表自己修改一下
--Method 1: TRIGGER
-------------------
--Base Table Definition
IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest
GO
CREATE TABLE CheckSumTest
(
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
vc1 varchar(1) NOT NULL,
vc2 varchar(1) NOT NULL
)
GO
INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b'
INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a'
GO
--Create Audit Summary Table to hold Meta-Data
IF OBJECT_ID('dbo.TableAuditSummary', 'U') IS NOT NULL DROP TABLE dbo.TableAuditSummary
CREATE TABLE dbo.TableAuditSummary
( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TableName sysname NOT NULL,
LastUpdate DATETIME NOT NULL,
LastExport DATETIME NOT NULL
)
GO
INSERT dbo.TableAuditSummary (TableName, LastUpdate, LastExport) VALUES ('dbo.CheckSumTest', GETDATE(), GETDATE())
GO
--Tables that need exporting
SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport
--Create Trigger on all Base Tables
--This fires on any insert/update/delete and writes new LastUpdate column for the table set to Current Date and Time
IF OBJECT_ID('dbo.trg_CheckSumTest_MaintainAuditSummary', 'TR') IS NOT NULL DROP TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary
GO
CREATE TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary
ON dbo.CheckSumTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF (object_id('dbo.CheckSumTest') IS NOT NULL)
UPDATE dbo.TableAuditSummary SET LastUpdate=GETDATE() WHERE TableName='dbo.CheckSumTest'
END
GO
--Make an Update
UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1
UPDATE dbo.CheckSumTest SET vc1='a', vc2='b' WHERE id=2
INSERT dbo.CheckSumTest
(
vc1,
vc2
)
VALUES
(
'c',
'c'
)DELETE CheckSumTest WHERE id='1'
--Check Meta-Data
SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport
--When we have Exported the data, we run the following to reset MetaData
UPDATE dbo.TableAuditSummary SET LastExport=GETDATE() WHERE LastUpdate>LastExport
Create table A_Log(ID int,ChangeType nvarchar(20),Date datetime default getdate())
go
create trigger tr_A on A
after insert,update,delete
as
if not exists(select 1 from deleted)
insert A_Log(ID,ChangeType) select ID,'新增' from inserted
else if not exists(select 1 from inserted)
insert A_Log(ID,ChangeType) select ID,'刪除' from deleted
else
insert A_Log(ID,ChangeType) select ID,'修改' from inserted i where not exists(select 1 from deleted where ID=i.ID)
常见的触发器有三种:分别应用于Insert , Update , Delete 事件。(SQL Server 2000定义了新的触发器,这里不提) 我为什么要使用触发器?比如,这么两个表: Create Table Student( --学生表
StudentID int primary key, --学号
....
) Create Table BorrowRecord( --学生借书记录表
BorrowRecord int identity(1,1), --流水号
StudentID int , --学号
BorrowDate datetime, --借出时间
ReturnDAte Datetime, --归还时间
...
) 用到的功能有:
1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
2.如果该学生已经毕业,我希望删除他的学号的同时,也删除它的借书记录。
等等。 这时候可以用到触发器。对于1,创建一个Update触发器: Create Trigger truStudent
On Student
for Update
-------------------------------------------------------
--Name:truStudent
--func:更新BorrowRecord 的StudentID,与Student同步。
--Use :None
--User:System
--Author: 懒虫 # SapphireStudio (www.chair3.com)
--Date : 2003-4-16
--Memo : 临时写写的,给大家作个Sample。没有调试阿。
-------------------------------------------------------
As
if Update(StudentID)
begin Update BorrowRecord
Set br.StudentID=i.StudentID
From BorrowRecord br , Deleted d ,Inserted i
Where br.StudentID=d.StudentID end
理解触发器里面的两个临时的表:Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
一个Update 的过程可以看作为:生成新的记录到Inserted表,复制旧的记录到Deleted表,然后删除Student记录并写入新纪录。 对于2,创建一个Delete触发器
Create trigger trdStudent
On Student
for Delete
-------------------------------------------------------
--Name:trdStudent
--func:同时删除 BorrowRecord 的数据
--Use :None
--User:System
--Author: 懒虫 # SapphireStudio (www.chair3.com)
--Date : 2003-4-16
--Memo : 临时写写的,给大家作个Sample。没有调试阿。
-------------------------------------------------------
As
Delete BorrowRecord
From BorrowRecord br , Delted d
Where br.StudentID=d.StudentID 从这两个例子我们可以看到了触发器的关键:A.2个临时的表;B.触发机制。
这里我们只讲解最简单的触发器。复杂的容后说明。
事实上,我不鼓励使用触发器。触发器的初始设计思想,已经被“级联”所替代。
create table A (id int,col int)
insert A
select 1,1 union all
select 2,2 union all
select 3,3 create table C(id int identity(1,1),UpdateType nvarchar(10))create trigger JiLu on A for insert,update,delete
as
begin
Declare @updateType as nvarchar(10)
set @updateType=''
if exists(select 1 from inserted)
begin
if exists(select 1 from deleted)
set @updateType='Update'
else
set @updateType='Insert'
end
else
set @updateType='Delete'
if @updateType<>''
insert C(UpdateType) select @updateType
endinsert A select 4,8select * from C--id UpdateType
------------- ----------
--1 Insert
--
--(1 row(s) affected)update A set col=8 where id=2select * from C--id UpdateType
------------- ----------
--1 Insert
--2 Update
--
--(2 row(s) affected)delete A where id=3select * from C--id UpdateType
------------- ----------
--1 Insert
--2 Update
--3 Delete
--
--(4 row(s) affected)
建议这个表的结构与原表相比加两个列,一个自动编号的列,可以显示顺序,另一个标志列,以保存是从inserted表还是从 deleted 表中来的,再加一个标志列,用于指示是什么操作,触发器中的写法前面都有,最简单的就是:
insert into c select *,2 as flg from inserted
insert intp c select *,2 as flg from deleted
这是更新触发器,插入触发器只有前一句,且标志 flg 为 1
删除触发器只有后一句,标志为 3.