I am using PostgreSQL server for my database project. I have two
tables in my schema A and B such that A contains records of employees
in an organization and B stores information about a two level
hierarchy - manager and employee. So B has as many rows for a manager
as there are employees who work under the manager. Each employee works
under one and only one manager.

Now manager and employee both are foreign keys referring to different
employee names (primary key) in relation A. Other relations contain
data that is decided by this hierarchy.

Now I want to create a trigger so that if an employee is promoted to
the post of a manager, the manager-employee row should be deleted from
relation B and the pertinent data in other relations should be updated
accordingly. I need the promoted employee's name to fetch this data
first. Since PostgreSQL does not supporting the "REFERENCING OLD ROW
AS..." clause, I cannot figure out how to refer to the employee that
is being promoted.

Is there any alternative to the REFERENCING... clause that will allow
me to get the employee's name and pass it as a parameter to the
function to be executed as an action of the trigger.

Thank you in advance.

Uncle Sam wrote on 28.10.2007 16:04:
It may not support the clause but you do have access to the old and new row:

http://www.postgresql.org/docs/8.2/s...l-trigger.html

Thomas