• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Oracle Trigger: delete newly inserted row

 
Ranch Hand
Posts: 884
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?



 
Ranch Hand
Posts: 51
Hibernate Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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;



/
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 884
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you all for the responses.
 
reply
    Bookmark Topic Watch Topic
  • New Topic