触发器

oracle ldboyghg 34℃ 0评论
CREATE OR REPLACE TRIGGER test_trg
BEFORE INSERT OR  DELETE ON TEST1
BEGIN
     CASE
     WHEN inserting THEN
         inser into test2 value :new;
     WHEN deleting THEN
         delete test2 where id=:old.id;
     END CASE;
END;
CREATE OR REPLACE TRIGGER MY_TGR
 BEFORE INSERT ON TAB_USER  FOR EACH ROW–对表的每一行触发器执行一次
DECLARE
NEXT_ID NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
 :NEW.ID := NEXT_ID;
–:NEW表示新插入的那条记录
END;
创建触发器
CREATE OR REPLACE TRIGGER trg_InsertIntoChargPkgInfo
   BEFORE INSERT ON DIM_WT_CHARG_PKG_INFO
   FOR EACH ROW
 BEGIN
   SELECT seq_WT_CHARG_PKG_INFO.nextval INTO :new.PK_SEQID FROM dual;
 END;
 当前用户 所有对象查询
 select object_name,object_type from user_objects where object_type=’PROCEDURE';
 — Create sequence
create sequence SEQ_LOG
minvalue 1
maxvalue 99999999
start with 21
increment by 1
cache 20;
create or replace trigger trg_users
    before insert or delete on users
    for each row
    begin
      case when inserting then
        insert into users_logs values(seq_log.nextval,’inserting’,:new.userName);
        case when deleting
        insert into users_logs values(seq_log.nextval,’deleting’,:old.userName);
      end;
 CREATE OR REPLACE TRIGGER trg_i_users
   BEFORE INSERT ON users
   FOR EACH ROW
 BEGIN
   SELECT trg_i_users.nextval INTO :new.userId FROM dual;
 END;
 insert into users(userName) values (dbms_random.string(‘o’,5));
 commit;
 delete from users u
 where u.username = ‘LZLNY’ ;
  commit;
select * from users;
select * from users_logs;
alter table users_logs modify (userName varchar2(5))

转载请注明:生命不息,奋斗不止 » 触发器

喜欢 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址