Database Reference
In-Depth Information
INSERT INTO salary_change_log
(salary_op,emp_name,old_salary,new_salary)
VALUES
('UPDATE',i_emp_name,old_salary,i_salary);
RETURN 'UPDATED USER ' || i_emp_name;
ELSE -- salary set to 0
DELETE FROM salaries WHERE
upper(emp_name) = upper(i_emp_name);
INSERT INTO
salary_change_log(salary_op,emp_name,old_salary)
VALUES ('DELETE',i_emp_name,old_salary);
RETURN 'DELETED USER ' || i_emp_name;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Now, drop the
audit
trigger (or the changes will be logged twice) and test the new
functionality:
postgres=# DROP TRIGGER audit_salary_change ON
salaries;
DROP TRIGGER
postgres=#
postgres=# SELECT set_salary('Fred',750);
-[ RECORD 1 ]------------------
set_salary | INSERTED USER Fred
postgres=# SELECT set_salary('frank',100);
-[ RECORD 1 ]-------------------
set_salary | INSERTED USER frank
postgres=# SELECT * FROM salaries ;
-[ RECORD 1 ]---
emp_name | Bob
salary | 1300
-[ RECORD 2 ]---
