Problem with delete trigger

Problem with delete trigger

Post by g.. » Sun, 31 Dec 1899 09:00:00



Hi:

I am getting this table mutating error from my trigger code. I know why
it happens but can not seem to find a work-around if I still want to
use trigger.

Basically I have a table HPXPRODUCTCATEGORY,

SQLWKS> desc HPXPRODUCTCATEGORY
Column Name                    Null?    Type
------------------------------ -------- ----
PRODUCTID                      NOT NULL NUMBER(9)
CATEGORYID                     NOT NULL NUMBER(9)
PRINCIPAL                      NOT NULL CHAR(1)

PRINCIPAL is a flag with values 'T' or 'F'. For each PRODUCTID, I want
to have one and only one PRINCIPAL ='T'.  The insert and update part in
trigger code works fine. The problem comes from delete part.

Here is what I want trigger code to do:

When deleting a record,
 if the record is non-principal record, do nothing
 end if;

 if the record is a principal record then
     if there are other record(s) with the same PRODUCTID,
        raise exception
     else
        no nothing
     end if;
 end if

The problem in my trigger code is I have "select ..." which results
table mutation error.

I probably can remove the "deleting" part out of trigger code and use
the front end java code to "check" before deleting record(s). But I
thought it would be nice if I could have all the code in one place
(trigger).

Any suggestions?

Thanks.

Guang

PS: Here is the trigger code:

------------------------------------------------------------------
CREATE OR REPLACE TRIGGER hpxtrg_category_chk_principal
before insert or update or delete on HPXPRODUCTCATEGORY
for each row
------------------------------------------------------------------
DECLARE

  lCOUNT                  INTEGER;
  lCOUNT_PRINCIPAL        INTEGER;
  lCOUNT_NON_PRINCIPAL    INTEGER;

BEGIN

  IF INSERTING or UPDATING THEN

    SELECT COUNT(*)
    INTO   lCOUNT
    FROM   HPXPRODUCTCATEGORY
    WHERE  PRODUCTID = :new.PRODUCTID
    AND    PRINCIPAL = 'T';

    IF lCOUNT = 0 THEN
      IF :new.PRINCIPAL != 'T' THEN
         RAISE_APPLICATION_ERROR (-20001, 'Error: This product has no
Principal Category');
      END IF;

    ELSIF lCOUNT = 1 THEN

      IF :new.PRINCIPAL = 'T' THEN
         RAISE_APPLICATION_ERROR (-20002, 'Error: This product already
has a Principal Category');
      END IF;

    ELSIF lCOUNT > 1 THEN
      RAISE_APPLICATION_ERROR (-20003, 'Error: This product has
multiple Principal Categories');
    ELSE
      NULL;
    END IF;

  END IF;

  IF DELETING THEN

    IF :old.PRINCIPAL != 'T' THEN
      -- deleting non-principal record, it's OK
      NULL;

    ELSIF :old.PRINCIPAL = 'T' THEN  -- try to delete principal

      SELECT COUNT(*)
      INTO   lCOUNT_NON_PRINCIPAL
      FROM   HPXPRODUCTCATEGORY
      WHERE  PRODUCTID = :old.PRODUCTID
      AND    PRINCIPAL != 'T';

      IF lCOUNT_NON_PRINCIPAL = 0 THEN
         --- no non-principal exists, it's OK to delete principal
         NULL;
      ELSIF lCOUNT_NON_PRINCIPAL > 0 THEN
         RAISE_APPLICATION_ERROR (-20004, 'Error: Non Principal
Category still exist');
      END IF;

    ELSE
      NULL;
    END IF;

  END IF;

END;
/

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Problem with delete trigger

Post by Jake de Haa » Sun, 31 Dec 1899 09:00:00


You need to create a statement trigger as opposed to a row trigger.  SQL in
trigger could be something where you'd define a cursor to do the
following and then within your loop, if you get at least one record back,
spit out the appropriate error.

SELECT productid, COUNT(*)
  INTO  tmp_productid, icount
  FROM table
 WHERE PRINCIPAL = 'T'
GROUP BY productid
HAVING COUNT(*) <> 1

Jake de Haan.

>     INTO   lCOUNT
>     FROM   HPXPRODUCTCATEGORY
>     WHERE  PRODUCTID = :new.PRODUCTID
>     AND    PRINCIPAL = 'T';

> Hi:

> I am getting this table mutating error from my trigger code. I know why
> it happens but can not seem to find a work-around if I still want to
> use trigger.

> Basically I have a table HPXPRODUCTCATEGORY,

> SQLWKS> desc HPXPRODUCTCATEGORY
> Column Name                    Null?    Type
> ------------------------------ -------- ----
> PRODUCTID                      NOT NULL NUMBER(9)
> CATEGORYID                     NOT NULL NUMBER(9)
> PRINCIPAL                      NOT NULL CHAR(1)

> PRINCIPAL is a flag with values 'T' or 'F'. For each PRODUCTID, I want
> to have one and only one PRINCIPAL ='T'.  The insert and update part in
> trigger code works fine. The problem comes from delete part.

> Here is what I want trigger code to do:

> When deleting a record,
>  if the record is non-principal record, do nothing
>  end if;

>  if the record is a principal record then
>      if there are other record(s) with the same PRODUCTID,
>         raise exception
>      else
>         no nothing
>      end if;
>  end if

> The problem in my trigger code is I have "select ..." which results
> table mutation error.

> I probably can remove the "deleting" part out of trigger code and use
> the front end java code to "check" before deleting record(s). But I
> thought it would be nice if I could have all the code in one place
> (trigger).

> Any suggestions?

> Thanks.

> Guang

> PS: Here is the trigger code:

> ------------------------------------------------------------------
> CREATE OR REPLACE TRIGGER hpxtrg_category_chk_principal
> before insert or update or delete on HPXPRODUCTCATEGORY
> for each row
> ------------------------------------------------------------------
> DECLARE

>   lCOUNT                  INTEGER;
>   lCOUNT_PRINCIPAL        INTEGER;
>   lCOUNT_NON_PRINCIPAL    INTEGER;

> BEGIN

>   IF INSERTING or UPDATING THEN

>     SELECT COUNT(*)
>     INTO   lCOUNT
>     FROM   HPXPRODUCTCATEGORY
>     WHERE  PRODUCTID = :new.PRODUCTID
>     AND    PRINCIPAL = 'T';

>     IF lCOUNT = 0 THEN
>       IF :new.PRINCIPAL != 'T' THEN
>          RAISE_APPLICATION_ERROR (-20001, 'Error: This product has no
> Principal Category');
>       END IF;

>     ELSIF lCOUNT = 1 THEN

>       IF :new.PRINCIPAL = 'T' THEN
>          RAISE_APPLICATION_ERROR (-20002, 'Error: This product already
> has a Principal Category');
>       END IF;

>     ELSIF lCOUNT > 1 THEN
>       RAISE_APPLICATION_ERROR (-20003, 'Error: This product has
> multiple Principal Categories');
>     ELSE
>       NULL;
>     END IF;

>   END IF;

>   IF DELETING THEN

>     IF :old.PRINCIPAL != 'T' THEN
>       -- deleting non-principal record, it's OK
>       NULL;

>     ELSIF :old.PRINCIPAL = 'T' THEN  -- try to delete principal

>       SELECT COUNT(*)
>       INTO   lCOUNT_NON_PRINCIPAL
>       FROM   HPXPRODUCTCATEGORY
>       WHERE  PRODUCTID = :old.PRODUCTID
>       AND    PRINCIPAL != 'T';

>       IF lCOUNT_NON_PRINCIPAL = 0 THEN
>          --- no non-principal exists, it's OK to delete principal
>          NULL;
>       ELSIF lCOUNT_NON_PRINCIPAL > 0 THEN
>          RAISE_APPLICATION_ERROR (-20004, 'Error: Non Principal
> Category still exist');
>       END IF;

>     ELSE
>       NULL;
>     END IF;

>   END IF;

> END;
> /

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

1. Problem With Delete Trigger, and question about netiquette...!

Hi All,

I'm trying to have a trigger on tblVisit, which logs a delete into an audit
table tblVisitAudit.
This is the trigger:

***** Procedure Start *****
Create TRIGGER trigVisitAudit_Delete ON [dbo].[tblVisit]
FOR DELETE
AS

 INSERT INTO tblVisitAudit (VisitRef, VisitAuditDate, CardNumber, DBLogin,
VisitAuditAction)
 SELECT Deleted.VisitID, GetDate(), Deleted.CardNumber,
Deleted.LastModifiedBy, 'DELETE'
 FROM Deleted INNER JOIN tblVisit ON Deleted.VisitID = tblVisit.VisitID
***** Procedure End *****

Nothing is inserted into the audit table. However I've verified that the
trigger event occurs by using the trigger below:

***** Procedure Start *****
Create TRIGGER trigVisitAudit_Delete ON [dbo].[tblVisit]
FOR DELETE
AS

 INSERT INTO tblVisitAudit (VisitRef, VisitAuditDate, CardNumber, DBLogin,
VisitAuditAction)
SELECT 0, GETDATE(), 1, 'TESTER', 'DELETE'
***** Procedure End *****

This updates tblVisitAudit with the test values when a record is deleted
from tblVisit.
My update and insert triggers work fine, however I just seem to have an
issue with the deleted table.

Any ideas, as I can't see the wood for the trees........

Finally, just a question about netiquette......
If I have two issues should I post them together?
The way I see it, I should post them separately, so people interested in one
don't have to follow the other.
But I can imagine that people would get funny about me posting two topics
within minutes of each other.

Alex

2. RC1 Packaged for Testing ...

3. problems with delete trigger

4. Purging in Sql Database

5. Problem with delete trigger

6. Filemaker pro server?

7. Replication to/from Workgroup Server

8. Trigger Problem (Delete Trigger)

9. instead of delete trigger delete data from table using execute and temporary table for deleted

10. Delete trigger not updating rows using delete from

11. delete trigger or cascade delete, which one??

12. Delete trigger shows nothing in Deleted table