Oracle触发器练习1

1.基本语法
CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <ACTION>
ON <table_name>

DECLARE
 <variable definitions>
BEGIN
  <trigger_code>
EXCEPTION
  <exception clauses>
END <trigger_name>;
2.练习举例
（1）创建表
创建一个消息接收表

-- Create table
create table XCOM.RECEIVEDMESSAGEBUFFERLOG
(
  RECEIVEDMESSAGEID NUMBER(38) not null,
  RECEIVEDLOGTIME   DATE,
  MESSAGEID         VARCHAR2(8),
  DESTINATION       VARCHAR2(8),
  LENGTH            NUMBER(4),
  BUFFERDATA        BLOB,
  PROCESSFLAG       NUMBER(2) default 0,
  NOTIFYLOGTIME     DATE,
  PROCESSLOGTIME    DATE,
  MEMO              VARCHAR2(1000) default ''
);

 

 

创建一个消息发送表

-- Create table
create table XCOM.SENTMESSAGEBUFFERLOG
(
  SENTMESSAGEID  NUMBER(38) not null,
  SENTLOGTIME    DATE,
  MESSAGEID      VARCHAR2(10),
  DESTINATION    VARCHAR2(8),
  LENGTH         NUMBER(5),
  BUFFERDATA     BLOB,
  SENDRESULT     NUMBER(2) default 0,
  PROCESSLOGTIME DATE,
  RESENDNUMBER   NUMBER(2) default -1,
  MEMO           VARCHAR2(1000) default ''
);

 

创建一个错误日志表

-- Create table
create table ERRORINFO
(
  E_ID  VARCHAR2(12),
  E_ERR VARCHAR2(50)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete, references, alter, index on ERRORINFO to XCOM;

 

2）编写触发器
当插入一条记录时，将"消息接收表"中的内容写入"消息发送表"

create or replace trigger trg_recMsgBufLog_insert
  after insert on xcom.receivedmessagebufferlog  
  for each row
declare
  -- local variables here
  v_id varchar2(20);
  v_errmsg varchar2(200);
  sentMsgId number(38);
begin
  v_id:=:new.messageid;
  --将接收缓存表的内容放入发送缓存表
  if :new.messageid='0A1D01' then
      select max(sentmessageid)+1 into sentMsgId from xcom.sentmessagebufferlog;
      -- memo
      insert into xcom.sentmessagebufferlog(sentmessageid,sentlogtime,messageid,destination,length,bufferdata,sendresult,processlogtime,resendnumber)
      values(sentMsgId,sysdate,'0A1D01','',:new.length,:new.bufferdata,0,sysdate,-1);
  end if;
exception  
  when others then
  --异常表
  begin
      v_errmsg := sqlcode || ':' || sqlerrm;
      insert into baosight.Errorinfo values(v_id,v_errmsg);
  end;   
end trg_recMsgBufLog_insert;