[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

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
0
alphamn
Asked:
alphamn
1 Solution
 
prajapati84Commented:
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
 
TaygerCommented:
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
 
alphamnAuthor Commented:
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
 
TaygerCommented:
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
 
Computer101Commented:
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now