trigger for updating a value

Question

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

Does http://stackoverflow.com/questions/8770386/ help? – Luke Woodward Dec 16 '12 at 13:25 — Luke Woodward, Dec 16 '12 at 13:25

a1ex07 · Accepted Answer · 2012-12-16T15:02:57.667

2

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.

edited Dec 16 '12 at 15:02

answered Dec 16 '12 at 13:15

a1ex07

36,826
12
90
103

@Manolo: you need to pass error code as well to `RAISE_APPLICATION_ERROR ` – a1ex07 Dec 16 '12 at 13:18
@Manolo: I'm not 100% sure what you want to achieve... If row-level trigger fires, a record exists in the table - your check doesn't make much sense. Maybe you want to check existence of some records in other tables ? – a1ex07 Dec 16 '12 at 14:25
Hi @a1ex07 I want that if for example I put update employee set salary=2000 where emp_id=100, and the code 100 does not exists, then it displays an error message – Layla Dec 16 '12 at 14:32
@Manolo: Can you post table structure[s]? Do you want to check existence of code=100 in the same table? – a1ex07 Dec 16 '12 at 14:43
I have just put the table structure, I want to check first if the code 100 exists before doing the update, that with the trigger, if it does not exists then displays an error message. Thanks – Layla Dec 16 '12 at 14:49

score 0 · Answer 2 · answered Dec 18 '12 at 01:34

0

"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).

answered Dec 18 '12 at 01:34

Jeffrey Kemp

59,135
14
106
158

trigger for updating a value

2 Answers2