关于Server Sql 2008触发器的使用

时间:2023-12-23 19:55:31

1、创建同一个服务器下同一个数据库实例两个不同数据库表同步方法

--====================================
-- Create database trigger
--==================================== --User表新增同步到副本数据库的User表里
create trigger tr_insert_User on China.dbo.[User]
for insert
as
set xact_abort on
insert China_Copy.dbo.[User](Id, Account, RealName, LastVisit, AuditStatus, Enabled)
select Id, Account, RealName, LastVisit, AuditStatus, Enabled from inserted
go --修改同步
create trigger tr_update_User on China.dbo.[User]
for update
as
set xact_abort on
update b set Enabled=i.Enabled,AuditStatus=i.AuditStatus
from China_Copy.dbo.[User] b,inserted i
where b.Id=i.Id
go --删除同步
create trigger tr_delete_User on China.dbo.[User]
for delete
as
set xact_abort on
delete b
from China_Copy.dbo.[User] b,deleted d
where b.Id=d.Id
go

2、查看当前库中所有的触发器:

SELECT * FROM Sysobjects WHERE xtype = 'TR'

3、查看当前库中所有的触发器和与之相对应的表:

SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type='TR'

4、显示触发器的定义:

EXEC sp_helptext '触发器名'

5、查看触发器的有关信息:

EXEC sp_help '触发器名'