[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Writting trigger in PlPgsql

Posted on 2006-11-05
6
Medium Priority
?
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

650 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