Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to create multiple triggers in one script?

Tags:

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 =)

like image 807
Kamron K. Avatar asked Aug 29 '11 16:08

Kamron K.


People also ask

Can we create 2 triggers on a table?

There can be only one first or last trigger for each statement on a table.

Can we create more than one trigger of same event on same 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.

Can we create multiple triggers on a table in Oracle?

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.

How the triggers will execute if two or more triggers?

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.


1 Answers

Add a forward slash on a new line after each trigger to execute the command in the buffer:

create trigger... ... end; / 
like image 89
Wolf Avatar answered Oct 21 '22 00:10

Wolf