关于Oracle的解发器

[liuhengwinner]

表1
create table TBL_AGENT_STATUS
(
USER_CODE VARCHAR2(12),
CURRENT_QUEUE_ID VARCHAR2(40),
)
表2
create table TBL_REST_WAITING_QUEUE
(
USER_CODE VARCHAR2(12),
QUEUE_LIST VARCHAR2(50),
REQ_TYPE INTEGER
)

当表1的 CURRENT_QUEUE_ID=2时删除表2中的 User_CODE相同的记录

[huan_lxyd]

CREATE OR REPLACE TRIGGER test
AFTER UPDATE ON TBL_AGENT_STATUS
FOR EACH ROW
BEGIN
IF :NEW.CURRENT_QUEUE_ID=2 then
DELETE FROM TBL_REST_WAITING_QUEUE WHERE USER_CODE IN(
SELECT DISTINCT USER_CODE FROM TBL_REST_WAITING_QUEUE
GROUP BY USER_CODE HAVING COUNT(1) > 1
);
END IF;
END;

[wflyxiaonian]

create or replace trigger test_tri
after delete on a
for each row
declare
-- local variables here
begin
delete from a02 where zd1 = :old.id;
end test_tri;

[春鼠哥]

CREATE OR REPLACE TRIGGER del_Code
AFTER UPDATE OF CURRENT_QUEUE_ID ON TBL_AGENT_STATUS FOR EACH ROW
BEGIN
if :old.CURRENT_QUEUE_ID=2 then
delete from TBL_REST_WAITING_QUEUE where USER_CODE=:old.USER_CODE;
end if;
END;

[jdsnhan]

SQL> select * from a;

ID USERID USERNAME
---------- -------------------- ----------
1 .net1 20120201
1 .net2 20120202
1 .net3 20120204
2 .net4 20120205
2 .net5 20120206
4 20120229
5 20120301
5 20120305
5 20120307
5 20120309
5 20120310
5 20120311
3 .net6 20120228

13 rows selected

SQL> select * from a02;

ZD1 ZD3
--- ---
1 A1
2 B1

SQL> delete from a where id=1;

3 rows deleted

SQL> commit;

Commit complete

SQL> select * from a;

ID USERID USERNAME
---------- -------------------- ----------
2 .net4 20120205
2 .net5 20120206
4 20120229
5 20120301
5 20120305
5 20120307
5 20120309
5 20120310
5 20120311
3 .net6 20120228

10 rows selected

SQL> select * from a02;

ZD1 ZD3
--- ---
2 B1


--触发器内容
create or replace trigger test_tri
after delete on a
for each row
declare
-- local variables here
begin
delete from a02 where zd1 = :old.id;
end test_tri;

[秋雨飘落]

CREATE OR REPLACE TRIGGER test
AFTER UPDATE ON TBL_AGENT_STATUS
FOR EACH ROW
BEGIN
IF :NEW.CURRENT_QUEUE_ID=2 then
DELETE FROM TBL_REST_WAITING_QUEUE WHERE USER_CODE IN(
SELECT DISTINCT USER_CODE FROM TBL_REST_WAITING_QUEUE
GROUP BY USER_CODE HAVING COUNT(1) > 1
);
END IF;
END;

[WSZHAO_SELECT]

create or replace trigger test_tri after delete on a for each row begin delete from a02 where zd1 = :old.id; end test_tri;

[sych888]

CREATE TRIGGER Test_Increase1 after insert or update ON TBL_AGENT_STATUS FOR EACH ROW begin if (:new.CURRENT_QUEUE_ID=2) then delete from TBL_REST_WAITING_QUEUE t where t.USER_CODE=:new.USER_CODE; end if; end; /

[gangma2]

create or replace trigger tri_delete after insert or update of CURRENT_QUEUE_ID on TBL_AGENT_STATUS for each row when (new.CURRENT_QUEUE_ID='2') begin delete from TBL_REST_WAITING_QUEUE where USER_CODE=:new.USER_CODE; end;