触发器

1、数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

2、触发器的类型
         语句级触发器
         在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。

         行级触发器（FOR EACH ROW）
         触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。

 

3、例子：

     一、语句级触发器：
     --当成功插入一个新员工后，自动打印“成功插入新员工”
    create trigger sayNewEmp
    after insert  --作用那个操作（之前还是之后）
    on emp --作用在那个表
    begin
       dbms_output.put_line('成功插入新员工');
    end;
   /

     二、行级触发器：
    /*
     数据确认

     涨后的工资不能少于涨前的工资
      */
     create or replace trigger checksal
      before update
     on emp
     for each row  --行级触发器  也就是说数据库中的每一行都要检查（每一行都对应一个员工）；
     begin
          --if 涨后的薪水 < 涨前的薪水  then
          if :new.sal < :old.sal then
          raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水,涨前:'||:old.sal||'   涨后:'||:new.sal);

         end if;
    end;
     /

 

 

 

4、不同服务器，不同数据库：

--不同服务器数据库之间的数据操作

--************************************************************************************

1、--创建链接服务器 

exec sp_addlinkedserver   'ITSV', ' ', 'SQLOLEDB', '远程服务器名或ip地址 ' 

exec sp_addlinkedsrvlogin  'ITSV', 'false ',null, '用户名', '密码' 

 

 

2、启动两台服务器的MSDTC服务 

MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。

 

3、打开双方的135端口

MSDTC服务依赖于RPC（Remote Procedure Call (RPC)）服务,RPC使用135端口，保证RPC服务启动，如果服务器有防火墙，保证135端口不被防火墙挡住。

使用“telnet IP 135”命令测试对方端口是否对外开放。也可用端口扫描软件（比如Advanced Port Scanner）扫描端口以判断端口是否开放

 

 

4、--如要创建触发器   

create   trigger   t_test   on   test   

for   insert,update,delete   

as 

--加上下面两句，否则会提示新事务不能登记到指定事务处理器

set    xact_abort   on  

begin  distributed   tran 

    delete   from   openrowset('sqloledb','xz';'sa';'',test.dbo.test)   

    where   id   in(select   id   from   deleted)   

    insert   into   openrowset('sqloledb','xz';'sa';'',test.dbo.test)   

    select   *   from   inserted   

commit tran

 

--查询示例 

select * from ITSV.数据库名.dbo.表名 

 

--导入示例 

select * into 表 from ITSV.数据库名.dbo.表名 

 

--以后不再使用时删除链接服务器 

exec sp_dropserver  'ITSV ', 'droplogins ' 

 

 

5、如果同一个服务器，不同的数据库（insert）：

 

一,user and org_employee同步:
 
use sgcadrept
go
create trigger save_employee      --定义触发器
on org_employee                        --作用在那个表
for insert                 --行级触发器
as
--begin
     declare @userid varchar(60)
     declare @loginname varchar(60)
     declare @username  varchar(100)
     declare @userpwd   varchar(50)
     declare @state     char(1)
     declare @logintime   varchar(20)
     declare @creator   varchar(60)
     declare @organid   varchar(100)
     declare @organ   varchar(200)
     select @userid = empid from inserted
     select @loginname = userid from inserted
     select @username = empname from inserted
     select @userpwd = 'e67c10a4c8fbfc0c400e047bb9a056a1'
     select @state = '1'
     select @logintime = GETDATE()
     select @creator = 'admin'
     select @organid = orgid from inserted
     select @organ = orgname from org_organization where orgid = @organid
     insert into ptzaozhuang.dbo.p_user (ptzaozhuang.dbo.p_user.userid,ptzaozhuang.dbo.p_user.loginname,ptzaozhuang.dbo.p_user.username
     ,ptzaozhuang.dbo.p_user.userpwd,ptzaozhuang.dbo.p_user.state,ptzaozhuang.dbo.p_user.logintime
     ,ptzaozhuang.dbo.p_user.creator,ptzaozhuang.dbo.p_user.organid,ptzaozhuang.dbo.p_user.organ) values(
     @userid,@loginname,@username,@userpwd,@state,@logintime,@creator,@organid,@organ)
--end

delete同步:

use sgcadrept
go
create trigger delete_employee
on org_employee
for delete
as
--begin
  declare @userid varchar(60)
  select @userid = empid from deleted
  update ptzaozhuang.dbo.p_user set ptzaozhuang.dbo.p_user.state='0'  where ptzaozhuang.dbo.p_user.userid=@userid
--end

update同步:

use sgcadrept
go
create trigger update_employee
on org_employee
for update
as
--begin
     declare @userid varchar(60)
     declare @loginname varchar(60)
     declare @username  varchar(100)
     declare @userpwd   varchar(50)
     declare @state     char(1)
     declare @logintime   varchar(20)
     declare @creator   varchar(60)
     declare @organid   varchar(100)
     declare @organ   varchar(200)
     select @userid = empid from inserted
     select @loginname = userid from inserted
     select @username = empname from inserted
     select @userpwd = 'e67c10a4c8fbfc0c400e047bb9a056a1'
     select @state = '1'
     select @logintime = GETDATE()
     select @creator = 'admin'
     select @organid = orgid from inserted
     select @organ = orgname from org_organization where orgid = @organid

     update ptzaozhuang.dbo.p_user set ptzaozhuang.dbo.p_user.loginname=@loginname,ptzaozhuang.dbo.p_user.username=@username
     ,ptzaozhuang.dbo.p_user.userpwd=@userpwd,ptzaozhuang.dbo.p_user.state=@state,ptzaozhuang.dbo.p_user.logintime=@logintime
     ,ptzaozhuang.dbo.p_user.creator=@creator,ptzaozhuang.dbo.p_user.organid=@organid,ptzaozhuang.dbo.p_user.organ=@organ
     where ptzaozhuang.dbo.p_user.userid=@userid
--end

二,xz08 and  org_organization 同步:

use sgcadrept
go
create trigger save_organization      --定义触发器
on org_organization                    --作用在那个表
for insert                 --行级触发器
as
--begin
     declare @code varchar(8)
     declare @code_name varchar(80)
     declare @code_abr1  varchar(80)
     declare @code_abr2   varchar(80)
     declare @code_level     float(53)
     declare @is_leaf     char(1)       --临时字段   判断转换使用
     declare @code_leaf   char(1)
     declare @sup_code   varchar(8)
     declare @code_assist   varchar(10)
     declare @invalid   varchar(2)
     declare @code_aname   varchar(100)
     declare @start_date   varchar(8)
     declare @stop_date   varchar(8)
     declare @iscustom   char(1)
     select @code = orgid from inserted
     select @code_name = orgname from inserted
     select @code_abr1 = @code_name
     select @code_abr2 = @code_name
     select @code_level = orglevel from inserted
     select @is_leaf = isleaf from inserted
     if @is_leaf<>'n'                         --判断转换 
        select @code_leaf = '1'
     if @is_leaf<>'y'
        select @code_leaf = '0'
     select @sup_code = parentorgid from inserted    --转换
     select @code_assist = '1'
     select @invalid = '1'
     select @code_aname = @code_name
     select @start_date = startdate from inserted
     select @stop_date = enddate from inserted
     select @iscustom = '1'
     insert into ptzaozhuang.dbo.xz08 (ptzaozhuang.dbo.xz08.code,ptzaozhuang.dbo.xz08.code_name,ptzaozhuang.dbo.xz08.code_abr1
     ,ptzaozhuang.dbo.xz08.code_abr2,ptzaozhuang.dbo.xz08.code_level,ptzaozhuang.dbo.xz08.code_leaf
     ,ptzaozhuang.dbo.xz08.sup_code,ptzaozhuang.dbo.xz08.code_assist,ptzaozhuang.dbo.xz08.invalid,ptzaozhuang.dbo.xz08.code_aname,ptzaozhuang.dbo.xz08.start_date
     ,ptzaozhuang.dbo.xz08.stop_date,ptzaozhuang.dbo.xz08.iscustom) values(
     @code,@code_name,@code_abr1,@code_abr2,@code_level,@code_leaf,@sup_code,@code_assist,@invalid,@code_aname,@start_date,@stop_date,@iscustom)
--end

delete同步:
update同步:

use sgcadrept
go
create trigger update_organization      --定义触发器
on org_organization                    --作用在那个表
for update                 --行级触发器
as
--begin
     declare @code varchar(8)
     declare @code_name varchar(80)
     declare @code_abr1  varchar(80)
     declare @code_abr2   varchar(80)
     declare @code_level     float(53)
     declare @is_leaf     char(1)       --临时字段   判断转换使用
     declare @code_leaf   char(1)
     declare @sup_code   varchar(8)
     declare @code_assist   varchar(10)
     declare @invalid   varchar(2)
     declare @code_aname   varchar(100)
     declare @start_date   varchar(8)
     declare @stop_date   varchar(8)
     declare @iscustom   char(1)
     select @code = orgid from inserted
     select @code_name = orgname from inserted
     select @code_abr1 = @code_name
     select @code_abr2 = @code_name
     select @code_level = orglevel from inserted
     select @is_leaf = isleaf from inserted
     if @is_leaf<>'n'                         --判断转换 
        select @code_leaf = '1'
     if @is_leaf<>'y'
        select @code_leaf = '0'
     select @sup_code = parentorgid from inserted    --转换
     select @code_assist = '1'
     select @invalid = '1'
     select @code_aname = @code_name
     select @start_date = startdate from inserted
     select @stop_date = enddate from inserted
     select @iscustom = '1'

     update ptzaozhuang.dbo.xz08 set ptzaozhuang.dbo.xz08.code_name=@code_name,ptzaozhuang.dbo.xz08.code_abr1=@code_abr1
     ,ptzaozhuang.dbo.xz08.code_abr2=@code_abr2,ptzaozhuang.dbo.xz08.code_level=@code_level,ptzaozhuang.dbo.xz08.code_leaf=@code_leaf
     ,ptzaozhuang.dbo.xz08.sup_code=@sup_code,ptzaozhuang.dbo.xz08.code_assist=@code_assist,ptzaozhuang.dbo.xz08.invalid=@invalid
     ,ptzaozhuang.dbo.xz08.code_aname=@code_aname,ptzaozhuang.dbo.xz08.start_date=@start_date,ptzaozhuang.dbo.xz08.stop_date=@stop_date
     ,ptzaozhuang.dbo.xz08.iscustom=@iscustom
     where ptzaozhuang.dbo.xz08.code=@code
--end

-------------------------------------------------------------------------------------------------------------------

use sgcadrept

go
create trigger save_employee      --定义触发器
on org_employee                        --作用在那个表
for insert                 --行级触发器
as
--begin
     declare @userid varchar(60)
     declare @loginname varchar(60)
     declare @username  varchar(100)
     declare @userpwd   varchar(50)
     declare @state     char(1)
     declare @logintime   varchar(20)
     declare @creator   varchar(60)
     declare @organid   varchar(100)
     declare @organ   varchar(200)
     select @userid = empid from inserted
     select @loginname = userid from inserted
     select @username = empname from inserted
     select @userpwd = 'e67c10a4c8fbfc0c400e047bb9a056a1'
     select @state = '1'
     select @logintime = GETDATE()
     select @creator = 'admin'
     select @organid = orgid from inserted
     select @organ = orgname from org_organization where orgid = @organid
     insert into ptzaozhuang.dbo.p_user (ptzaozhuang.dbo.p_user.userid,ptzaozhuang.dbo.p_user.loginname,ptzaozhuang.dbo.p_user.username
     ,ptzaozhuang.dbo.p_user.userpwd,ptzaozhuang.dbo.p_user.state,ptzaozhuang.dbo.p_user.logintime
     ,ptzaozhuang.dbo.p_user.creator,ptzaozhuang.dbo.p_user.organid,ptzaozhuang.dbo.p_user.organ) values(
     @userid,@loginname,@username,@userpwd,@state,@logintime,@creator,@organid,@organ)
--end

go
create trigger delete_employee
on org_employee
for delete
as
--begin
  declare @userid varchar(60)
  select @userid = empid from deleted
  update ptzaozhuang.dbo.p_user set ptzaozhuang.dbo.p_user.state='0'  where ptzaozhuang.dbo.p_user.userid=@userid
--end

go
create trigger update_employee
on org_employee
for update
as
--begin
     declare @userid varchar(60)
     declare @loginname varchar(60)
     declare @username  varchar(100)
     declare @userpwd   varchar(50)
     declare @state     char(1)
     declare @logintime   varchar(20)
     declare @creator   varchar(60)
     declare @organid   varchar(100)
     declare @organ   varchar(200)
     select @userid = empid from inserted
     select @loginname = userid from inserted
     select @username = empname from inserted
     select @userpwd = 'e67c10a4c8fbfc0c400e047bb9a056a1'
     select @state = '1'
     select @logintime = GETDATE()
     select @creator = 'admin'
     select @organid = orgid from inserted
     select @organ = orgname from org_organization where orgid = @organid

     update ptzaozhuang.dbo.p_user set ptzaozhuang.dbo.p_user.loginname=@loginname,ptzaozhuang.dbo.p_user.username=@username
     ,ptzaozhuang.dbo.p_user.userpwd=@userpwd,ptzaozhuang.dbo.p_user.state=@state,ptzaozhuang.dbo.p_user.logintime=@logintime
     ,ptzaozhuang.dbo.p_user.creator=@creator,ptzaozhuang.dbo.p_user.organid=@organid,ptzaozhuang.dbo.p_user.organ=@organ
     where ptzaozhuang.dbo.p_user.userid=@userid
--end

go
create trigger save_organization      --定义触发器
on org_organization                    --作用在那个表
for insert                 --行级触发器
as
--begin
     declare @code varchar(8)
     declare @code_name varchar(80)
     declare @code_abr1  varchar(80)
     declare @code_abr2   varchar(80)
     declare @code_level     float(53)
     declare @is_leaf     char(1)       --临时字段   判断转换使用
     declare @code_leaf   char(1)
     declare @sup_code   varchar(8)
     declare @code_assist   varchar(10)
     declare @invalid   varchar(2)
     declare @code_aname   varchar(100)
     declare @start_date   varchar(8)
     declare @stop_date   varchar(8)
     declare @iscustom   char(1)
     select @code = orgid from inserted
     select @code_name = orgname from inserted
     select @code_abr1 = @code_name
     select @code_abr2 = @code_name
     select @code_level = orglevel from inserted
     select @is_leaf = isleaf from inserted
     if @is_leaf<>'n'                         --判断转换 
        select @code_leaf = '1'
     if @is_leaf<>'y'
        select @code_leaf = '0'
     select @sup_code = parentorgid from inserted    --转换
     select @code_assist = '1'
     select @invalid = '1'
     select @code_aname = @code_name
     select @start_date = startdate from inserted
     select @stop_date = enddate from inserted
     select @iscustom = '1'
     insert into ptzaozhuang.dbo.xz08 (ptzaozhuang.dbo.xz08.code,ptzaozhuang.dbo.xz08.code_name,ptzaozhuang.dbo.xz08.code_abr1
     ,ptzaozhuang.dbo.xz08.code_abr2,ptzaozhuang.dbo.xz08.code_level,ptzaozhuang.dbo.xz08.code_leaf
     ,ptzaozhuang.dbo.xz08.sup_code,ptzaozhuang.dbo.xz08.code_assist,ptzaozhuang.dbo.xz08.invalid,ptzaozhuang.dbo.xz08.code_aname,ptzaozhuang.dbo.xz08.start_date
     ,ptzaozhuang.dbo.xz08.stop_date,ptzaozhuang.dbo.xz08.iscustom) values(
     @code,@code_name,@code_abr1,@code_abr2,@code_level,@code_leaf,@sup_code,@code_assist,@invalid,@code_aname,@start_date,@stop_date,@iscustom)
--end

go
create trigger update_organization      --定义触发器
on org_organization                    --作用在那个表
for update                 --行级触发器
as
--begin
     declare @code varchar(8)
     declare @code_name varchar(80)
     declare @code_abr1  varchar(80)
     declare @code_abr2   varchar(80)
     declare @code_level     float(53)
     declare @is_leaf     char(1)       --临时字段   判断转换使用
     declare @code_leaf   char(1)
     declare @sup_code   varchar(8)
     declare @code_assist   varchar(10)
     declare @invalid   varchar(2)
     declare @code_aname   varchar(100)
     declare @start_date   varchar(8)
     declare @stop_date   varchar(8)
     declare @iscustom   char(1)
     select @code = orgid from inserted
     select @code_name = orgname from inserted
     select @code_abr1 = @code_name
     select @code_abr2 = @code_name
     select @code_level = orglevel from inserted
     select @is_leaf = isleaf from inserted
     if @is_leaf<>'n'                         --判断转换 
        select @code_leaf = '1'
     if @is_leaf<>'y'
        select @code_leaf = '0'
     select @sup_code = parentorgid from inserted    --转换
     select @code_assist = '1'
     select @invalid = '1'
     select @code_aname = @code_name
     select @start_date = startdate from inserted
     select @stop_date = enddate from inserted
     select @iscustom = '1'

     update ptzaozhuang.dbo.xz08 set ptzaozhuang.dbo.xz08.code_name=@code_name,ptzaozhuang.dbo.xz08.code_abr1=@code_abr1
     ,ptzaozhuang.dbo.xz08.code_abr2=@code_abr2,ptzaozhuang.dbo.xz08.code_level=@code_level,ptzaozhuang.dbo.xz08.code_leaf=@code_leaf
     ,ptzaozhuang.dbo.xz08.sup_code=@sup_code,ptzaozhuang.dbo.xz08.code_assist=@code_assist,ptzaozhuang.dbo.xz08.invalid=@invalid
     ,ptzaozhuang.dbo.xz08.code_aname=@code_aname,ptzaozhuang.dbo.xz08.start_date=@start_date,ptzaozhuang.dbo.xz08.stop_date=@stop_date
     ,ptzaozhuang.dbo.xz08.iscustom=@iscustom
     where ptzaozhuang.dbo.xz08.code=@code
--end

 

 

 

总结：insert基于inserted虚拟表，有数据库提供（没增加一条，会在虚拟表中保存一份）；

           delete基于deleted虚拟表；

           update基于deleted和inserted，先删除修改的记录，再增加修改后的记录，所以update触发器可以基于insert触发器编写；

 

补充：

       insert触发器：当增加某个表记录时，动态创建一个表；

use sgcadrept
go
create trigger save_employee      --定义触发器
on org_employee                        --作用在那个表
for insert                 --行级触发器
as
--begin
     declare @userid varchar(60)
     declare @loginname varchar(60)
     declare @username  varchar(100)
     declare @userpwd   varchar(50)
     declare @state     char(1)
     declare @logintime   varchar(20)
     declare @creator   varchar(60)
     declare @organid   varchar(100)
     declare @organ   varchar(200)
     declare @createsql varchar(200)            --拼接创建动态表语句
     declare @createkey varchar(100)            --创建主建语句
     select @userid = empid from inserted
     select @loginname = userid from inserted
     select @username = empname from inserted
     select @userpwd = 'e67c10a4c8fbfc0c400e047bb9a056a1'
     select @state = '1'
     select @logintime = GETDATE()
     select @creator = 'admin'
     select @organid = orgid from inserted
     select @organ = orgname from org_organization where orgid = @organid
   set @createsql = 'CREATE TABLE [ptzaozhuang].[dbo].[A01_RST_'+@loginname+'] ([A0000] numeric(18) NOT NULL ,[ISVALID] int NOT NULL ,[SORTID] int NULL ,[A0201AA] varchar(68) NULL ,[A0215] varchar(4) NULL)'
     set @createkey = 'ALTER TABLE [ptzaozhuang].[dbo].[A01_RST_'+@loginname+'] ADD PRIMARY KEY ([A0000], [ISVALID])'
     insert into ptzaozhuang.dbo.p_user (ptzaozhuang.dbo.p_user.userid,ptzaozhuang.dbo.p_user.loginname,ptzaozhuang.dbo.p_user.username
     ,ptzaozhuang.dbo.p_user.userpwd,ptzaozhuang.dbo.p_user.state,ptzaozhuang.dbo.p_user.logintime
     ,ptzaozhuang.dbo.p_user.creator,ptzaozhuang.dbo.p_user.organid,ptzaozhuang.dbo.p_user.organ) values(
     @userid,@loginname,@username,@userpwd,@state,@logintime,@creator,@organid,@organ)

     exec (@createsql)    --执行创建表
     exec (@createkey)
--end

注：拼接创建表语句时，不能换行，创建表语句和创建主键语句必须分开执行；