CODEWITHSUNDEEP
sqloracle-databasestored-procedurestriggers
Nidhin_toms
Nidhin_toms

Reputation: 737

trigger to stop the execution of SQl query

CREATE OR REPLACE trigger million_trigger
BEFORE INSERT or UPDATE on employee
FOR EACH ROW
WHEN (new.SALARY>1000000)

DECLARE
txt EXCEPTION;

BEGIN
if INSERTING or UPDATING then 

    RAISE txt ;

end if;

EXCEPTION
    WHEN txt THEN
        DBMS_OUTPUT.PUT_LINE('SALARY TOO HIGH');
end;
/

Hello, I created a trigger which is checks if the salary from table employee is greater than 1,000,000. If the salary is greater, then the trigger is supposed to stop the execution of an insert statement from a stored procedure. The trigger has been successfully created but when I insert a record with salary > 1,000,000 nothing happens. ( the record gets inserted - which is not supposed to happen ) Any idea?

Upvotes: 1

Views: 4404

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

You are catching the exception so the trigger doesn't throw an error. Since the trigger doesn't throw an error, the INSERT statement continues and, ultimately, succeeds. If you happen to have enabled serveroutput in your session, you would see the "Salary too high" message but you should never depend on data written to the dbms_output buffer being read by anyone.

If you want to stop the execution of the INSERT statement, you would need to remove your exception handler. Most likely, you would also want to change how you are raising the exception

IF( :new.salary > 1000000 )
THEN
  RAISE_APPLICATION_ERROR( -20001, 'Salary too high' );
END IF;

Upvotes: 4

Related Questions