Oracle Trigger: delete newly inserted row

[Chengwei Lee]

Hi all,

I'd like to have a trigger that will check a specific column of the newly inserted row to see if it has a specific value. If it does, then delete the row. I'd written a trigger like below, but it failed to compile. Could someone help me with it? Or am I using the wrong method?

    
CREATE OR REPLACE TRIGGER FEE_TID
    AFTER INSERT ON FEE
    REFERENCING NEW AS newRow
    FOR EACH ROW
    
when (newRow.ID = 'trial')
BEGIN
        DELETE FEE WHERE ID = 'trial'
    END FEE_TID;

[Gene Hilpert]

Dont let the data get in the first place use a Before insert trigger.

Something like this

CREATE OR REPLACE TRIGGER yourtablename_bir
BEFORE INSERT
ON yourtablename
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE

BEGIN
if (new.ID = 'trial') then
RAISE trial_error;
);


EXCEPTION
WHEN trial_error THEN
raise_application_exception(-20001,'This is a trial record it will not be inserted');
END;



/

[Shital Supase]

HELLO,

WRITE A TRIGGER AS GIVEN BELOW, WHICH WILL INSERT A NEW ROW IF , ITS FULFILLING THE CRITERIA FOR SALARY VALUE, OTHERWISE WILL RAISE AN ERROR & WILL CALL A PROCEDURE WHICH WILL ROLLBACK THE JOB.

------------------TRIGGER--------------------------------------------

CREATE OR REPLACE TRIGGER triggername BEFORE INSERT ON yourtablename
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW

DECLARE

BEGIN

IF :NEW.SALARY < 1000 THEN
RAISE_APPLICATION_ERROR('SORRY U CANT INSERT THIS ROW');
ROLLPROC;
END IF;

END;
/

--------------------------------------------------------------------------

--------------------PROCEDURE---------------------------------------------
CREATE OR REPLACE PROCEDURE ROLLPROC IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ROLLBACK DONE');

END;
/
---------------------------------------------------------------------------


TRY TO WORK WITH THIS, YOUR PROBLEM WILL DEFINATELY GET SOLVED.

NICE TIME.

[Chengwei Lee]

Thank you all for the responses.