sqlserver 触发器

/*
   2013年6月26日0:09:34
   用户: admin
   服务器: zhouxx-PC
   数据库: QZ56
   应用程序: 
*/

/* 为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Table_1
	(
	id int NOT NULL IDENTITY (1,1),name nchar(10) NULL,status int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
	PK_Table_1 PRIMARY KEY CLUSTERED 
	(
	id
	) WITH( STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Table_1 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT




CREATE TABLE dbo.Table_2
	(
	id int NOT NULL IDENTITY (1,status int NULL,opt_a nchar(20)
	) ;
	

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date,>
-- Description:	<Description,>
-- =============================================
CREATE TRIGGER trg_table_1
   ON  table_1
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
   IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
   begin
     insert into table_2(name,status,opt_a)
     select name,'insert' from inserted
     --values(inserted.id,inserted.name,inserted.status,'insert')
   end
   else
   begin
     IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
       begin
     insert into table_2(name,'update-new' from inserted
     --values(inserted.id,'update-new')  
      insert into table_2(name,opt_a)
      select name,'update-old' from deleted
     --values(deleted.id,deleted.name,deleted.status,'update-old')      
       end
     else
     begin
          insert into table_2(name,opt_a)
          select name,'delete' from deleted
     --values(deleted.id,'delete')
     end
   end
   
END
GO

set identity_insert table_1 OFF
go

set identity_insert table_2 OFF
go

set identity_insert table_1 ON
go
set identity_insert table_2 ON
go

insert into table_1(id,name,status)values(1,'jim',1)
go
insert into table_1(name,status)values('tom',2)
go
insert into table_1(name,status)values('jery',3)
go
insert into table_1(name,status)values('tony',4)
go

--set identity_insert table_1 OFF
--go

select * from table_1;
select * from table_2;

update table_1 set name='a' where id=20;
delete from table_1 where id=20;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!