Solved

Writting trigger in PlPgsql

Posted on 2006-11-05
6
419 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
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell script 13 120
MIcrosoft SQL 2014 Database Copy Question 16 59
Access 2016 Query with Proper Case logic 2 48
Database maintenance 36 98
Read about achieving the basic levels of HRIS security in the workplace.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

680 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