CODEWITHSUNDEEP
sqldatabaseoracle-databasetriggerssql-delete
Djdj
Djdj

Reputation: 3

trigger to delete from two tables simultaneously in sql oracle apex

I have two tables: teachers and advisers, which have some repeating values(a person can be both teacher and adviser) teacher has primary key: Teachers_id, which is foreign key in advisers table I want to make a trigger that would delete values from adviser, if they were deleted in teacher

I tried to do it like this, but I have a mistake, which I cannot find

create or replace TRIGGER delete_teacher 
AFTER 
DELETE on TEACHER  
FOR EACH ROW
declare 
pragma autonomous_transaction;
BEGIN
    DELETE FROM ADVISER  
    WHERE ADVISER.T_id = TEACHER.T_id;  
END;

I'm working in oracle apex

Upvotes: 0

Views: 157

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

That's

CREATE OR REPLACE TRIGGER delete_teacher
   AFTER DELETE
   ON teacher
   FOR EACH ROW
BEGIN
   DELETE FROM adviser where t_id = :old.t_id;
END;
  • trigger doesn't have to be (and shouldn't be) an autonomous transaction (why do you think it should be?)
  • reference deleted row with its :old pseudorecord

On the other hand, if you created the foreign key constraint so that it deletes child records automatically, you wouldn't have to create any triggers.

SQL> create table teacher
  2    (t_id number primary key,
  3     name varchar2(10));

Table created.

SQL> create table adviser
  2    (a_id number primary key,
  3     t_id number references teacher on delete cascade,    --> this
  4     name varchar2(10));

Table created.

Sample rows:

SQL> insert all
  2    into teacher values (1, 'Little')
  3    into teacher values (2, 'Foot')
  4    into adviser values (100, 1, 'Little')
  5  select * from dual;

3 rows created.

Delete teacher whose t_id = 1; Oracle will delete both master and detail records:

SQL> delete from teacher where t_id = 1;

1 row deleted.

SQL> select * from teacher;

      T_ID NAME
---------- ----------
         2 Foot

SQL> select * from adviser;

no rows selected         --> see? No Little here.

SQL>

Upvotes: 1

Related Questions