Database Reference
In-Depth Information
CREATE OR REPLACE FUNCTION log_salary_change ()
RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO
salary_change_log(salary_op,emp_name,new_salary)
VALUES (TG_OP,NEW.emp_name,NEW.salary);
ELSIF TG_OP = 'UPDATE' THEN INSERT
INTO
salary_change_log(salary_op,emp_name,old_salary,new_salary)
VALUES
(TG_OP,NEW.emp_name,OLD.salary,NEW.salary);
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO
salary_change_log(salary_op,emp_name,old_salary)
VALUES (TG_OP,NEW.emp_name,OLD.salary);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER audit_salary_change
AFTER INSERT OR UPDATE OR DELETE ON salaries
FOR EACH ROW EXECUTE PROCEDURE
log_salary_change ();
Now, let's test out some salary management:
postgres=# INSERT INTO salaries
values('Bob',1000);
INSERT 0 1
postgres=# UPDATE salaries set salary = 1100
where emp_name = 'Bob';
UPDATE 1
postgres=# INSERT INTO salaries
values('Mary',1000);
INSERT 0 1
