CODEWITHSUNDEEP
oracle-databaseplsqltriggersoracle-sqldeveloperorder-of-execution
Prakshi
Prakshi

Reputation: 1

Maintain dependency between two tables while executing triggers in oracle

How we can maintain dependency between two tables while executing triggers in oracle?? As we know we can use follows clause if we have single table and multiple triggers based on that table but my concern is that is there any way if we have 2 different tables and there are different triggers based on these table so how can we maintain some proper sequence in this case.

For example: --Table1:

CREATE TABLE emp(
empId number PRIMARY KEY,
FirstName varchar2(20),
LastName varchar2(20),
Email varchar2(25),
PhoneNo varchar2(25),
Salary number(8)
);

--Table 2:

CREATE TABLE emp_1(
empId number PRIMARY KEY,
FirstName varchar2(20),
LastName varchar2(20),
Email varchar2(25),
PhoneNo varchar2(25),
Salary number(8)
);

Trigger on EMP:

CREATE OR replace TRIGGER TRIGGER_emp 
BEFORE INSERT OR AFTER UPDATE ON emp
FOR EACH ROW 
BEGIN  
  
    dbms_output.put_line ('MY EXECUTE ORDER FOR EMP IS SECOND -EXECUTED');  
END; 
/

Trigger on EMP1:

CREATE OR replace TRIGGER TRIGGER_emp1
BEFOR INSERT OR AFTER UPDATE ON emp_1 
FOR EACH ROW 
BEGIN  
  
    dbms_output.put_line ('MY EXECUTE ORDER FOR EMP IS FIRST -EXECUTED');  
END; 
/

Now i want this trigger TRIGGER_emp1 will execute first and then this trigger will execute TRIGGER_emp last. Is it possible to do so in oracle

Please guide me in this.

Upvotes: 0

Views: 107

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

You seem to be a clone of user who recently posted two questions regarding the same subject. It was said that you can't do that. Triggers "belong" to a single table, which means that

  • you insert (or update) values in emp table, which then
  • fires trigger trigger_emp which does something
    • in your case, it just displays a message (you might, or might not see; that depends on a tool you use)
  • as nothing happened to table emp1, trigger trigger_emp1 won't fire - why would it?
    • if you want it to fire, then let the first trigger (trigger_emp) affect rows in emp1 table (insert a row or update existing values)
      • once you do that, trigger_emp1 will do whatever it does

Maybe, just maybe you should consider creating a stored procedure which contains code that works with rows in both emp and emp1 table. Then, instead of relying on triggers and worry whether they will fire, when will they fire and in which sequence, your main transaction would actually call the procedure which would then do the job (and affect rows in emp and emp1, in any order you want - that's easy to do as you'd just rearrange statements within the procedure).

Upvotes: 1

Related Questions