Trigger does not work when DELETING

[Arthur Dylewski]

I have the following trigger (bottom of the page). As I insert a new row
into "INDIVIDUAL" table, a new row is created in the "SECURITY" table (if it
meets the criteria). Here is the problem: If I delete a row from
"INDIVIDUAL" table, the trigger does not work (the DELETE statement is never
executed). If I execute the statement from SQLPLUS it works (the proper row
is deleted).

Thanks

(this is the actual trigger)
-----------------------------------------------------
CREATE OR REPLACE TRIGGER "TRS"."T_INDIVIDUAL_SECURITY" AFTER INSERT OR
DELETE ON "INDIVIDUAL" FOR EACH ROW
declare
IDVal Number;
Pass Number;

begin
if :new.org_id=111 or :new.org_id=121 or :new.org_id=260 or :new.org_id=281
then
IDVal := :new.id;
Pass := 9999;

if inserting then
insert into trs.security$ values (IDVal, Pass, 5);
else
delete from trs.security$ where id=IDVal;
end if;
end if;
end;
-----------------------------------------------------

[SAP Users]

Hi,

I think :new.id is not available in a delete, since there is no new value
for the column. Try :old.id

"Arthur Dylewski" <adyl...@infobal.com> wrote in message
news:vnTk5.63564$UO.1...@news22.bellglobal.com...

[Arthur Dylewski]

Actually, I tried it too. It doesn't work either.

"SAP Users" <som...@sap.com> wrote in message
news:8n112i$hgq$1...@news1.wdf.sap-ag.de...

[Erika Grondzakova]

Hello,

It should work. Try this example:

SQL> create table my_table(id number);

Table created.

SQL> create table my_security_table(id number);

Table created.

SQL> CREATE OR REPLACE TRIGGER my_trigger
2 AFTER INSERT OR DELETE ON my_table FOR EACH ROW
3
3 begin
4
4 if inserting then
5 insert into my_security_table values (:new.id);
6 else
7 delete from my_security_table where id=:old.id;
8 end if;
9
9 end;
10 /

Trigger created.

SQL> insert into my_table values(1);

1 row created.

SQL> select * from my_security_table;

ID
---------
1

SQL> delete from my_table where id = 1;

1 row deleted.

SQL> select * from my_security_table;

no rows selected

Check if there is not some logical error somewhere in your conditions.

Hth,

Erika