I am trying to create multiple triggers with only uploading one script into an Oracle DB / APEX workspace, and running it once.
Here is a brief script compared to the one im trying to use:
create or replace trigger "BI_TEC_ROLES" before insert on "TEC_ROLES" for each row begin if :NEW."ROLE_ID" is null then select "TEC_ROLES_SEQ".nextval into :NEW."ROLE_ID" from dual; end if; end; create or replace trigger "BI_TEC_STATUSES" before insert on "TEC_STATUSES" for each row begin if :NEW."STATUS_ID" is null then select "TEC_STATUSES_SEQ".nextval into :NEW."STATUS_ID" from dual; end if; end; create or replace trigger "BI_TEC_SUBS" before insert on "TEC_SUBS" for each row begin if :NEW."SUB_ID" is null then select "TEC_SUBS_SEQ".nextval into :NEW."SUB_ID" from dual; end if; end;
I have tried putting GO in between each individual block, but still only creates the first trigger then gives me an error for the second saying:
Error(7,1): PLS-00103: Encountered the symbol "CREATE"
I am hoping that it is possible to do this. Thank you very much for your time and interest =)
There can be only one first or last trigger for each statement on a table.
You can create multiple triggers for the same subject table, event, and activation time. The order in which those triggers are activated is the order in which the triggers were created.
You can create multiple triggers of the same type ( BEFORE , AFTER , or INSTEAD OF ) that fire for the same statement on the same table. The order in which Oracle Database fires these triggers is indeterminate.
Sometimes the business logic dictates that we need to define two triggers on a table that must fire in a specific order on the same table action. For example when we insert rows in a table (INSERT statement) two triggers must fire and the second must fire after the first one for our logic to be implemented correctly.
Add a forward slash on a new line after each trigger to execute the command in the buffer:
create trigger... ... end; /
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With