登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

梦想之鹰的天空

天高任鸟飞......放飞....心情..........放飞.....梦想

 
 
 

日志

 
 

触发器示例  

2011-04-22 13:59:02|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

跨数据库的触发器,表中有自增长字段和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

  评论这张
 
阅读(686)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018