CODEWITHSUNDEEP
plsqltriggers
Layla
Layla

Reputation: 5446

trigger for updating a value

I am a newbie in PLSQL and I would like to create a trigger that checks first if there is a record in a table before making an update. The code I got so far is:

CREATE OR REPLACE TRIGGER table_bu
BEFORE UPDATE ON employee
FOR EACH ROW
DECLARE
    v_employee_id:=employee.employee_ID%TYPE;
BEGIN
    SELECT employee_id INTO v_employee_id FROM employee;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR (-20001,'data not found');
END;

How I can create a trigger that checks up if a record exists in the table and if it does not exists does not allow the update. My table estructure is:

employee_id NUMBER
employee_name VARCHAR(20)
employee_salary NUMBER
...

Thanks

Upvotes: 1

Views: 204

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

"How I can create a trigger that checks up if a record exists in the table and if it does not exists does not allow the update."

There is really only one practical way to do this - use a referential constraint (foreign key).

Upvotes: 0

a1ex07
a1ex07

Reputation: 37364

You are on a wrong way. The trigger as it is will throw runtime 'Mutating table' error even after fixing syntax error - you missed semicolon after raise_application_error(also it should take 2 arguments, not one). Correct syntax :

EXCEPTION
WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR (-20001, 'data not found'); -- 1st parameter -error code

Update

As far as I understand the updated version of the question, you want to show error if record doesn't exist. The problem with row level trigger approach is that it won't be executed if nothing is found due to condition in WHERE. The simplest way is to check number of rows affected on client side and raise an error there. Or you can write a procedure that checks sql%rowcount after executing desired update, and then throw an exception if it's 0.
If you prefer to do in a hard way, you can create package variable which of type employee.employee_ID%TYPE, before update statement level trigger that resets variable (say set it to null), after update row level trigger that sets this variable to NEW.employee_ID, and after update statement level trigger that throws an exception if the variable is null. Note: this will properly work for individual updates only.

Upvotes: 2

Related Questions