Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Trigger to compare between current month and previous

Posted on 2012-03-21
3
Medium Priority
?
438 Views
Last Modified: 2012-04-16
I have the following table: -
tbl_EmpMonthSalaries
fld_EmpId
fld_Alw_Ded_Tag
fld_Alw_Ded_Id
fld_Alw_Ded_Amount
fld_FromPeriod
fld_ToPeriod

Open in new window


I need help in write Oracle trigger to make the following: -
Create new table to store result there
Make compare between current month allwances and deduction with last allwances and deductions withdraw the same employee
If there any different should store the result in a new Table like
fld_EmpId
fld_AlwDedTag
fld_AlwDedId
fld_FromPeriod
fld_ToPeriod
fld_AlwDedAmount
fld_OldFromPeriod
fld_OldToPeriod
fld_OldAlwded

Open in new window

Each time run this trigger delete the new table and recreated again
The trigger should be run after commit done (That only one time)

Any help
0
Comment
Question by:egovernment
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
flow01 earned 2000 total points
ID: 37746217
try this query first, if it works you can make a view of it

fld_EmpId
,fld_Alw_Ded_Tag
,fld_Alw_Ded_Id
,fld_FromPeriod
,fld_ToPeriod
,fld_Alw_Ded_Amount fld_AlwDedAmount -- why diffirent name her
,lag(FromPeriod) OVER (PARTITION BY fld_EmpId ORDER BY fld_FromPeriod) fld_OldFromPeriod
,lag(fld_ToPeriod) OVER (PARTITION BY fld_EmpId ORDER BY fld_FromPeriod) fld_OldToPeriod
,lag(fld_Alw_Ded_Amount) OVER (PARTITION BY fld_EmpId ORDER BY fld_FromPeriod) fld_OldAlwded
from tbl_EmpMonthSalaries

if you still want the new_table

create trigger xx
AFTER INSERT OR UPDATE OR DELETE
on  tbl_EmpMonthSalaries
is
begin
   delete from new_table; -- but it should exist the first time
   -- execute immediate 'truncate new_table'  -- will be fast but i don't know if allowed here
   insert into new_table
   -- same query as before
   select ...;
end;

-- you can't do it on commit : this is a statement trigger it will fire for each insert, insert,update,delete
-- if could worry about performance
-- create a package with an id-list to hold the id's action is necessary
in a before delete for each row trigger add the :old.fld_EmpId to the list
in a before insert, update for each row trigger add the  :new.fld_EmpId to the list
in the after statement trigger
delete the rows with id's that are in the list  and add new records base on the id-list
0
 

Author Comment

by:egovernment
ID: 37758511
I don't need to write a query or view I need a trigger
0
 
LVL 21

Expert Comment

by:flow01
ID: 37758805
You want to achieve a goal, a trigger might be 1 way to achieve it. I suggested another way, not knowing your goal.
But there is also a trigger in my post:
see create trigger
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

876 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