asked on

Writting trigger in PlPgsql

I am new to postgresql and is trying to implement trigger.
I have a master table EmpMaster where the primary key EmpId is a auto incremented field.
I want to insert a new entry in the transaction table LeavesDetails as an when a new employee joins in.
On the execution of insert statement
insert into EmpMaster(empName,deptName,location) values('John','DeptAcc','Head office')
i want a new entry for the same employee be made in the leavesDetails table through a trigger.

My problem is how do i refer to newly generated EmpId from EmpMaster table . In MSSQL there is a concept of magic tables (inserted, deleted). How does one handle it in plpgSQL.

thanks in advance

prajapati84

Try this one...

You can get the newly generated EmpID using inserted as shown below...If you have declared any variable named @empid in trigger, you can get the inserted value in the variable:

@empid = select EmpID from inserted

or

You can get the deleted value of EmpID,

@empid = select EmpID from deleted

Suppose you want to insert the values of EmpID, EmpName into other table in trigger, then...

Insert into TableName(EmpID,EmpName) values(select EmpID from inserted,select EmpName from inserted)

If you have any query abt it, please post again...

Regards,
Mukesh

Tayger

Hello

You already gave the answer yourself. ;) You should be able to access the columns of the newly inserted record in the master table by the keyword "new". In the insert statement you only need to type:

INSERT INTO leavesDetails
(EmpId
,...)
VALUES
(new.EmpId,
...)

Make sure the trigger is created by triggering AFTER insert record and for each row. Doing a "SELECT MAX (EmpID) INTO variable FROM mastertable" should be possible as well, but the "new" option does not need to access the master table to get the EmpID.

Regards
Tayger

alphamn

ASKER

Hello

with select max(empid) i will not get the correct result always. Till the time one gives the statement to fetch the max empid some else might insert a new record then the result would be incorrect.

I got the solution from http://www.varlena.com/GeneralBits/14.php . Its working fine.

Any way thanks for the responses.

Tayger

Did you access the DB by Python? I was thinking you tried an insert inside the DB. That way you probably would have seen that it seems to work inside but not over plpyhton.
What confuses me a bit is that plpython (the mentioned version) is causing a dropped server while you dont get the trigger properly to run. I also dont see the soluion there.
Could you let us know your solution? Im generally interested in solutions and someone looking for answers might find yours then. ;)

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial