Re: 7813 trigger tribulations

krose_at_athena.nwafc.nf.ca schrieb:
>
> Ahhhh - im having a heap of trouble. (bear with me - im a virgin trigger
> writer !!)
>
> Im trying to write a trigger on INSERT such that the insert will be cancelled
> should a reecord satisfying certain conditions exist:
>
> CREATE OR REPLACE TRIGGER
> BEFORE INSERT FR_COMMON_DETAILS
> FOR EACH ROW
>
> BEGIN
> IF ((SELECT COUNT(*) FROM FR_COMMON_DETAILS WHERE ......) = 0) then
> return true; *****
> ELSE
> return false; *****
> END IF;
>
> END;
> ***** basically i dont know what to return here such that the insert will be
> cancelled or to permit it to go ahead??? Im not even sure how to return or if
> there is a form of return. How do I correctly write the lines marked with
> *****.
>
> Any help greatly appreciated.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Hi,

your code

CREATE OR REPLACE TRIGGER
BEFORE INSERT FR_COMMON_DETAILS
FOR EACH ROW BEGIN
IF ((SELECT COUNT(*) FROM FR_COMMON_DETAILS WHERE ......) = 0) then

          return true; *****
ELSE
          return false; *****
END IF; END; won't work:

1. produces a TBALE IS MUTATING ERROR
2. won't work this way

So try:

CREATE OR REPLACE TRIGGER
BEFORE INSERT FR_COMMON_DETAILS
FOR EACH ROW
l_COUNT NUMBER;
BEGIN
SELECT
        COUNT(*) --prefere COUNT(PK-field)
INTO
        l_COUNT
FROM
        FR_COMMON_DETAILS
WHERE
        ......
AND
        ROWID <> :NEW.ROWID -- prevents TABLE IS MUTATING, by excluding new record
;
IF (l_COUNT <>|= 0) then

        raise_application_error(-20000, 'Your Error'); END IF; END; HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Fri Jan 22 1999 - 00:33:35 CST