Toad World® Forums

sequences and triggers


#1

CREATE OR REPLACE TRIGGER “tsu_PHONE_NUMBERS_SEQ_PHONE__0” AFTER UPDATE OF “PHONE_ID”

BEGIN

END;

I want to be able to specify an ID and only use the sequence if the ID is null or not supplied for an insert, so I need to specify my own trigger that is associated with the sequence.

How can I alter the trigger that TDM generates for a sequence? I looked through the generated script and it looks like the following:

ON “PHONE_NUMBERS” FOR EACH ROWRAISE_APPLICATION_ERROR(-20010,‘Cannot update column “PHONE_ID” in table “PHONE_NUMBERS” as it uses sequence.’);

Message was edited by: cpisz_515


#2

Hi,

we have two suggestions for you:

  1. Disable the Generate Triggers for Update of Columns That Use Sequences
    and write the trigger manually as any other trigger or
  2. Rewrite system scripts. If you write us how exactly the trigger should be written than we can help you with this task.

Regards,

Vaclav


#3

CREATE OR REPLACE TRIGGER “TR_SEQ_MAILLIST” BEFORE INSERT ON “MAILLIST”

REFERENCING NEW AS NEW

new.MAL_SEQUENCE is null

WHEN (

DECLARE

BEGIN

END;

Just want to add the when clause so it only happens when supplied a null, otherwise use the supplied id
FOR EACH ROW)select SEQ_MAILLIST.nextval into :new.MAL_SEQUENCE from dual;


#4

Hi,

find attached zipped user-defined packages and instructions how to install the packages to Toad Data Modeler.

If you wish to modify it, open Script Explorer and in folder Generation locate script “PERCodeGeneratorOR_user”. In the script there are two functions. One is for Oracle 9/10 and the other for Oracle 11.

Regards,

Vaclav & Mario
How to install user package.pdf (269 KB)


#5

Hi,

find attached zipped user-defined packages and instructions how to install the packages to Toad Data Modeler.

If you wish to modify it, open Script Explorer and in folder Generation locate script “PERCodeGeneratorOR_user”. In the script there are two functions. One is for Oracle 9/10 and the other for Oracle 11.

Regards,

Vaclav & Mario
GenTrigSequences.zip (1.97 KB)