Re: Triggers seem to be compiled only randomly

Andreas Mosmann wrote:
> Hi,
>
> there is a script that generates triggers.[2]
>
> The problem is the following behavior: both [1] scripts are created by
> the same script, same schema, nearly same trigger body, same tables, but:
> in the first script I had to insert the schema before the table name (if
> not there were no way to compile) and the second worked fine without
> changes. All things are similar to equal, the only difference are the
> detail table and of course the trigger name, but in the first script I
> had to insert schema, the second works without.
> Is there any explaination (I can reproduce this many times, there are
> some similar scripts with same differences)
>
> I work on Oracle 9.2i, OS is WinXP
>
> Andreas
>
> [1]
>
> CREATE TRIGGER SABISDAT.itzVerarbeitungsSchlUC0
> BEFORE UPDATE
> ON SABISDAT.tzVerarbeitungsSchl
> ^^^^^^^^^
> FOR EACH ROW
> BEGIN
> IF :NEW.cID <> :OLD.cID THEN
> UPDATE
> tbBuchungen
> SET
> cIDVerarb = :NEW.cID
> WHERE
> cIDVerarb = :OLD.cID;
> END IF;
> END;
>
> CREATE TRIGGER SABISDAT.itzVerarbeitungsSchlUC1
> BEFORE UPDATE
> ON tzVerarbeitungsSchl
> FOR EACH ROW
> BEGIN
> IF :NEW.cID <> :OLD.cID THEN
> UPDATE
> tbBuchungsVorlagen
> SET
> cIDVerarb = :NEW.cID
> WHERE
> cIDVerarb = :OLD.cID;
> END IF;
> END;
>
> [2]
> In that case the triggers controls, that if the primary key of the
> mastertable changes the all foreign keys of detailtable are changed. (I
> guess it will not work, because a constraint will prevent the update,
> but this is not my actual problem)

Either as Sybrand suggests merge them if appropriate or learn to use utlrp.sql located in the $ORACLE_HOME/rdbms/admin directory.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)