My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?
The INSTEAD OF trigger replaces the original insert. So the code:
Insert into dbo.TestTable (Name,Age,DOB,Address)
Select Name,Age,DOB,Address From inserted;
...is executed instead of the original insert:
Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');
The actions in the instead of trigger are completed before constraints are checked, and before any AFTER triggers fire.
When the instead of trigger completes, a row has been added to the TestTable and CopyTable tables. Note though that the transaction is still active at this point, so the changes are not yet permanent.
Next, the after trigger fires. It sees the name 'Waleed' in the inserted table, prints a message, but does not rollback the changes made so far in the transaction (including the rows added by the instead of trigger). No row is added to the AuditTable.
If the condition that checked for the name 'Waleed' also performed a rollback, everything would be rolled back, and no rows would be added to any table.
Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.
The above works fine after fixing your trigger and sample data:
create or replace trigger updB
after update on B
for each row
declare
v varchar2(5);
begin
v := :new.val;
end;
/
This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.
SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;
2 rows created.
SQL> commit;
Commit complete.
SQL> update A set val = 'true';
Cek cek: false
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from a;
ID VAL
---------- ----------
a true
SQL> select * from b;
ID VAL
---------- ----------
a true
For the output:
Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:
create or replace trigger updA
after update on A
for each row
declare
pragma autonomous_transaction;
cek varchar2(5);
begin
commit;
cek := :new.val;
dbms_output.put_line('Cek cek: ' || cek);
update B set val = :new.val where id = :new.id;
commit;
end;
/
Best Answer
This is an answer which follows the table definitions you posted:
However, the example you posted does not match the column names you gave later. This means you are not posting your actual code, and this makes everybody’s job harder.
Maybe this is only a toy example to learn how to use MySQL triggers. As such, it’s fine. In production, the very idea to change the price in the main
producttable upon insertion of a new promotion is questionable. A better design would be not to change theproducttable, addstart_dateandend_datecolumns to thepromotiontable, and read the current prices either from a view which applies the active promotions for the day, or, if you have millions of products and performance becomes an issue, from a temporary table regenerated each day (and possibly replicated to slave servers... you know your scale).