Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for Update (Windows)
Trigger for Update [message #427430] Thu, 22 October 2009 10:33 Go to next message
namb
Messages: 35
Registered: September 2009
Member
Dear All,

I have the following issue.

I want to create a statement level trigger for updating the data from one table that is the child table with the following fields

table name emp_att_child

Emp_code
Time_in
Time_out
Total_hrs

time in and time out are when the emp enters and leave the organization. Total hours calcaute the total working hours.

the parent table emp_att_parent also contains all these fields

This child table contains the records with modified data which has to be updated in the parent table using a update trigger defined at a statement level so that update trigger is called for each row automatically. Can someone please help how to define the trigger at the statement level. If possible please provide examples or the code.

Thanks in advance
Re: Trigger for Update [message #427434 is a reply to message #427430] Thu, 22 October 2009 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide.

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Trigger for Update [message #427439 is a reply to message #427430] Thu, 22 October 2009 10:56 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
namb wrote on Thu, 22 October 2009 16:33
Dear All,

I have the following issue.

I want to create a statement level trigger for updating the data from one table that is the child table with the following fields

table name emp_att_child

Emp_code
Time_in
Time_out
Total_hrs

time in and time out are when the emp enters and leave the organization. Total hours calcaute the total working hours.

the parent table emp_att_parent also contains all these fields

This child table contains the records with modified data which has to be updated in the parent table


That's a design decision you're almost certainly going to regret. It's far simpler to not store calculated fields and just calculate them when you need to display them.
Doing it this way always leads to performance issues (due to locking problems) and almost always bugs.


namb wrote on Thu, 22 October 2009 16:33
This child table contains the records with modified data which has to be updated in the parent table using a update trigger defined at a statement level so that update trigger is called for each row automatically. Can someone please help how to define the trigger at the statement level. If possible please provide examples or the code.


Think you need to need to read up on triggers as that doesn't make sense. Statement level triggers are not called for each row - row level triggers are. Statement level triggers can't see which rows have been updated either.

Re: Trigger for Update [message #427442 is a reply to message #427439] Thu, 22 October 2009 11:07 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, read the following topic about the same subject: Trigger-summing values. A quite long topic but it is worth to carefully and slowly read it from top to bottom.

Regards
Michel

[Updated on: Thu, 22 October 2009 11:11]

Report message to a moderator

Previous Topic: write messages from pl/sql
Next Topic: incorrect number of arguments for default constructor
Goto Forum:
  


Current Time: Fri Dec 09 06:13:29 CST 2016

Total time taken to generate the page: 0.06997 seconds