跨数据库的触发器,表中有自增长字段和Image字段,比较麻烦,写了一个简单的触发器实现两张表同步的要求
create trigger tri_ins_table1
on dbo.table1
for insert
as
begin
insert into .database2.dbo.table1
(BADGENUMBER,
SSN,
NAME,
GENDER,
TITLE,
PAGER,
BIRTHDAY,
HIREDDAY,
STREET,
CITY,
STATE,
ZIP,
OPHONE,
FPHONE,
VERIFICATIONMETHOD,
DEFAULTDEPTID,
SECURITYFLAGS,
ATT,
INLATE,
OUTEARLY,
OVERTIME,
SEP,
HOLIDAY,
MINZU,
PASSWORD,
LUNCHDURATION,
MVerifyPass,
privilege,
InheritDeptSch,
InheritDeptSchClass,
AutoSchPlan,
MinAutoSchInterval,
RegisterOT,
InheritDeptRule,
EMPRIVILEGE,
CardNo
)
select
BADGENUMBER,
SSN,
NAME,
GENDER,
TITLE,
PAGER,
BIRTHDAY,
HIREDDAY,
STREET,
CITY,
STATE,
ZIP,
OPHONE,
FPHONE,
VERIFICATIONMETHOD,
DEFAULTDEPTID,
SECURITYFLAGS,
ATT,
INLATE,
OUTEARLY,
OVERTIME,
SEP,
HOLIDAY,
MINZU,
PASSWORD,
LUNCHDURATION,
MVerifyPass,
privilege,
InheritDeptSch,
InheritDeptSchClass,
AutoSchPlan,
MinAutoSchInterval,
RegisterOT,
InheritDeptRule,
EMPRIVILEGE,
CardNo
from inserted
end
create trigger tri_update_table1
on dbo.table1
for update
as
begin
declare @BADGENUMBER varchar(24)
declare @SSN varchar(20)
declare @NAME varchar(40)
declare @GENDER varchar(8)
declare @TITLE varchar(20)
declare @PAGER varchar(20)
declare @BIRTHDAY datetime
declare @HIREDDAY datetime
declare @STREET varchar(80)
declare @CITY varchar(2)
declare @STATE varchar(2)
declare @ZIP varchar(12)
declare @OPHONE varchar(20)
declare @FPHONE varchar(20)
declare @VERIFICATIONMETHOD smallint
declare @DEFAULTDEPTID smallint
declare @SECURITYFLAGS smallint
declare @ATT smallint
declare @INLATE smallint
declare @OUTEARLY smallint
declare @OVERTIME smallint
declare @SEP smallint
declare @HOLIDAY smallint
declare @MINZU varchar(8)
declare @PASSWORD varchar(20)
declare @LUNCHDURATION smallint
declare @MVerifyPass varchar(10)
declare @privilege int
declare @InheritDeptSch smallint
declare @InheritDeptSchClass smallint
declare @AutoSchPlan smallint
declare @MinAutoSchInterval int
declare @RegisterOT smallint
declare @InheritDeptRule smallint
declare @EMPRIVILEGE smallint
declare @CardNo varchar(20)
declare @PHOTO varbinary(8000)
set @PHOTO = (select cast(PHOTO as varbinary(8000)) from table1 where BADGENUMBER in (select BADGENUMBER from
inserted))
declare @Notes varbinary(8000)
set @Notes = (select cast(Notes as varbinary(8000)) from table1 where BADGENUMBER in (select BADGENUMBER from
inserted))
set @SSN = (select SSN from inserted)
set @NAME = (select NAME from inserted)
set @GENDER = (select GENDER from inserted)
set @TITLE = (select TITLE from inserted)
set @PAGER = (select PAGER from inserted)
set @BIRTHDAY = (select BIRTHDAY from inserted)
set @HIREDDAY = (select HIREDDAY from inserted)
set @STREET = (select STREET from inserted)
set @CITY = (select CITY from inserted)
set @STATE = (select STATE from inserted)
set @ZIP = (select ZIP from inserted)
set @OPHONE = (select OPHONE from inserted)
set @FPHONE = (select FPHONE from inserted)
set @VERIFICATIONMETHOD = (select VERIFICATIONMETHOD from inserted)
set @DEFAULTDEPTID = (select DEFAULTDEPTID from inserted)
set @SECURITYFLAGS = (select SECURITYFLAGS from inserted)
set @ATT = (select ATT from inserted)
set @INLATE = (select INLATE from inserted)
set @OUTEARLY = (select OUTEARLY from inserted)
set @OVERTIME = (select OVERTIME from inserted)
set @SEP = (select SEP from inserted)
set @HOLIDAY = (select HOLIDAY from inserted)
set @MINZU = (select MINZU from inserted)
set @PASSWORD = (select PASSWORD from inserted)
set @LUNCHDURATION = (select LUNCHDURATION from inserted)
set @MVerifyPass = (select MVerifyPass from inserted)
set @privilege = (select privilege from inserted)
set @InheritDeptSch = (select InheritDeptSch from inserted)
set @InheritDeptSchClass = (select InheritDeptSchClass from inserted)
set @AutoSchPlan = (select AutoSchPlan from inserted)
set @MinAutoSchInterval = (select MinAutoSchInterval from inserted)
set @RegisterOT = (select RegisterOT from inserted)
set @InheritDeptRule = (select InheritDeptRule from inserted)
set @EMPRIVILEGE = (select EMPRIVILEGE from inserted)
set @CardNo = (select CardNo from inserted)
update .database2.dbo.table1
set
SSN = @SSN,
NAME = @NAME,
GENDER = @GENDER,
TITLE = @TITLE,
PAGER = @PAGER,
BIRTHDAY = @BIRTHDAY,
HIREDDAY = @HIREDDAY,
STREET = @STREET,
CITY = @CITY,
STATE = @STATE,
ZIP = @ZIP,
OPHONE = @OPHONE,
FPHONE = @FPHONE,
VERIFICATIONMETHOD = @VERIFICATIONMETHOD,
DEFAULTDEPTID = @DEFAULTDEPTID,
SECURITYFLAGS = @SECURITYFLAGS,
ATT = @ATT,
INLATE = @INLATE,
OUTEARLY = @OUTEARLY,
OVERTIME = @OVERTIME,
SEP = @SEP,
HOLIDAY = @HOLIDAY,
MINZU = @MINZU,
PASSWORD = @PASSWORD,
LUNCHDURATION = @LUNCHDURATION,
MVerifyPass = @MVerifyPass,
PHOTO = @PHOTO,
Notes = @Notes,
privilege = @privilege,
InheritDeptSch = @InheritDeptSch,
InheritDeptSchClass = @InheritDeptSchClass,
AutoSchPlan = @AutoSchPlan,
MinAutoSchInterval = @MinAutoSchInterval,
RegisterOT = @RegisterOT,
InheritDeptRule = @InheritDeptRule,
EMPRIVILEGE = @EMPRIVILEGE,
CardNo = @CardNo
where BADGENUMBER in (select BADGENUMBER from inserted)
end
create trigger tri_delete_table1
on dbo.table1
for delete
as
begin
delete .database2.dbo.table1 where BADGENUMBER in (select BADGENUMBER from deleted)
end
评论