ORACLE 触发器

1 、ORACLE 触发器的增删改

//删除
CREATE OR REPLACE TRIGGER tr_del_test
BEFORE DELETE
   ON T_SYS_TEST
   ON T_SYS_TEST
   FOR EACH ROW
BEGIN
   INSERT INTO t_sys_test_update(id ,username,password)
       VALUES(:old.id, :old.username, :old.password);
END;
//插入
CREATE OR REPLACE TRIGGER tr_del_test
BEFORE INSERT ON t_sys_test
       FOR EACH ROW
BEGIN
       INSERT INTO t_sys_test_update VALUES(:NEW.ID ,:NEW.USERNAME,:NEW.PASSWORD);
END;
//修改
CREATE OR REPLACE TRIGGER tr_del_test
AFTER UPDATE ON t_sys_test
       FOR EACH ROW
BEGIN
       UPDATE t_sys_test_update  SET username = :new.username WHERE id = 3;
END;
//循环

CREATE OR REPLACE TRIGGER tr_del_test
BEFORE INSERT ON t_sys_test
       FOR EACH ROW
DECLARE
       V_NUM NUMBER := 0;
BEGIN
       WHILE V_NUM < 10 LOOP  V_NUM := V_NUM+1;
             INSERT INTO t_sys_test_update VALUES(:NEW.ID ,:NEW.USERNAME,:NEW.PASSWORD);

                 
       END LOOP;
END;
//动态查询数据再循环

CREATE OR REPLACE TRIGGER tr_del_test
BEFORE INSERT ON t_sys_test
       FOR EACH ROW
DECLARE
       VAR_ID T_SYS_USER.ID%TYPE;
       VAR_SUM NUMBER:=0;
       CURSOR USERCURSOR IS SELECT ID FROM T_SYS_USER;
BEGIN  
       OPEN USERCURSOR;
       LOOP
            FETCH USERCURSOR INTO VAR_ID;
            EXIT WHEN USERCURSOR%NOTFOUND;
                 INSERT INTO t_sys_test_update VALUES(VAR_ID ,:NEW.USERNAME,:NEW.PASSWORD,VAR_ID);
                 VAR_SUM:=VAR_ID+VAR_SUM;
                 DBMS_OUTPUT.put_line('RESULT:'||VAR_ID);
               
            END LOOP;
            CLOSE USERCURSOR;

END;
//添加修改、删除、插入

CREATE OR REPLACE TRIGGER tr_del_test
BEFORE INSERT OR DELETE OR UPDATE
      ON t_sys_test
      FOR EACH ROW
DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION; --自治事物
       VAR_ID T_SYS_TEST.ID%TYPE;
       CURSOR USERCURSOR IS SELECT ID FROM T_SYS_TEST;
BEGIN
--触发插入操作
IF INSERTING THEN
       OPEN USERCURSOR;
       LOOP
            FETCH USERCURSOR INTO VAR_ID;
            EXIT WHEN USERCURSOR%NOTFOUND;
                  INSERT INTO t_sys_test_update VALUES(:NEW.ID,:NEW.USERNAME,:NEW.PASSWORD,VAR_ID,1);
       END LOOP;
       COMMIT;
       CLOSE USERCURSOR;
--触发删除操作
ELSIF DELETING THEN
      OPEN USERCURSOR;
       LOOP
            FETCH USERCURSOR INTO VAR_ID;
            EXIT WHEN USERCURSOR%NOTFOUND;
                  INSERT INTO t_sys_test_update VALUES(:OLD.ID,:OLD.USERNAME,:OLD.PASSWORD,VAR_ID,2);

       END LOOP;
       COMMIT;
       CLOSE USERCURSOR;
--触发修改操作
ELSE
       OPEN USERCURSOR;
       LOOP
            FETCH USERCURSOR INTO VAR_ID;
            EXIT WHEN USERCURSOR%NOTFOUND;
                  INSERT INTO t_sys_test_update VALUES(:NEW.ID,:NEW.USERNAME,:NEW.PASSWORD,VAR_ID,3);

       END LOOP;
       COMMIT;
       CLOSE USERCURSOR;
END IF;
END;

2、查看表的触发器

select * from user_triggers where table_name=table_name