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;
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;
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;
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;
after delete on a
for each row
declare
-- local variables here
begin
delete from a02 where zd1 = :old.id;
end test_tri;
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;
after delete on a
for each row
begin
delete from a02 where zd1 = :old.id;
end test_tri;
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;
/
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;