Improve company productivity with a Business Account.Sign Up

x
?
Solved

Writting trigger in PlPgsql

Posted on 2006-11-05
6
Medium Priority
?
432 Views
Last Modified: 2006-12-03
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
0
Comment
Question by:alphamn
5 Comments
 
LVL 5

Expert Comment

by:prajapati84
ID: 17880532
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
0
 
LVL 2

Expert Comment

by:Tayger
ID: 17884886
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
0
 

Author Comment

by:alphamn
ID: 17887424
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.
0
 
LVL 2

Expert Comment

by:Tayger
ID: 17888608
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. ;)
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18066322
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question