CODEWITHSUNDEEP
sqloracle-databasestored-procedurestriggersddl
Nidhin_toms
Nidhin_toms

Reputation: 737

oracle sql trigger to prevent insertion of record

CREATE OR REPLACE trigger million_trigger
BEFORE INSERT or UPDATE on employee
FOR EACH ROW
WHEN (new.SALARY>1000000)
BEGIN
if inserting then       
    RETURN ;    
end if;

if updating then 
    RETURN ;
end if;
end;
/

BEGIN
    PKG_insertrec.insertrec(121,'Mark',2000000);
END;
/

Hello all , I wrote a trigger to prevent the insertion of a record if the SALARY is above 1,000,000. I have already written a stored procedure insertrec which is in a package PKG_insertrec. The problem is that the trigger does not work even though the trigger was successfully compiled.

Upvotes: 1

Views: 168

Answers (1)

Mureinik
Mureinik

Reputation: 311163

IMHO, your approach is wrong - you don't need triggers for such a simple input validation - just used constraints.

ALTER TABLE employee ADD CONSTRAINT employee_salary CHECK (salary <= 1000000);

If you really want to use a trigger, you should raise instead of return when an invalid input is given.

Upvotes: 6

Related Questions