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