Re: Delete trigger does not fire

Mohnish wrote:
>
> Hello,
> If somebody can point out where am I making a mistake .I am
> trying to insert a row into the tblElec if a Delete action is
> performed on tblDoc
>
> My script is as follows.
>
> DROP TABLE "WWW".TBLELECT;
>
> CREATE TABLE "WWW".TBLELECT
> (ELEC_HIS_ID NUMBER(10,0) DEFAULT 0 NOT NULL,
> ITEM_NO NUMBER(10,0) NOT NULL,
> ATTACHMENT NUMBER(10,0) NOT NULL,
> EFFECTIVE_DATE DATE NULL,
> USER_ID VARCHAR2(30) NULL,
> CONSTRAINT TEH_PRIMARY PRIMARY KEY (ELEC_HIS_ID));
>
> DROP SEQUENCE "WWW".SEQ_TEH_ITEM_NO_ATTACH;
> CREATE SEQUENCE "WWW".SEQ_TEH_ITEM_NO_ATTACH INCREMENT BY 1 START WITH
> 1 MAXVALUE 9999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
>
> CREATE OR REPLACE TRIGGER "WWW".TRIGELEC_HIS_INSERT BEFORE
> INSERT ON "WWW"."TBLELEC" REFERENCING OLD AS o NEW AS n
> FOR EACH ROW
> WHEN (n.ELEC_HIS_ID = 0 OR n.ELEC_HIS_ID = NULL)
> DECLARE
> CINT NUMBER;
> BEGIN
> SELECT SEQ_TEH_ITEM_NO_ATTACH.NEXTVAL INTO CINT FROM DUAL;
> :n.ELEC_HIS_ID := CINT;
> END;
> /
>
> REM************************Trigger created to fire on delete on table
> REM***** TBLDOC and it should insert the deleted row into TBLELEC
>
> CREATE OR REPLACE TRIGGER "WWW".TRIGELECT BEFORE
> DELETE ON "WWW"."TBLDOC"
> REFERENCING OLD AS o NEW AS n
> FOR EACH ROW
>
> BEGIN
>
> INSERT INTO TBLELEC(ITEM_NO , ATTACHMENT ,
> EFFECTIVE_DATE, USER_ID )
> VALUES(:o.ITEM_NO , :o.ATTACHMENT , :o.EFFECTIVE_DATE, :o.USER_ID );
>
> END;
> /
>
> I'll greatly appreciate any help extended.
>
> MMM
So has the TBLDOC table changed (invalidating your trigger)? I have no other clue other than I think the INSERT trigger also has a problem.
Are you sure the delete trigger doesn't fire? I think it is the insert trigger.
(HINT: x=NULL is always FALSE!)

HTH   Ed Prochak Received on Fri Oct 05 2001 - 22:49:29 CDT