Solved

Oracle Trigger to compare between current month and previous

Posted on 2012-03-21
3
407 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 20

Accepted Solution

by:
flow01 earned 500 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 20

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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

830 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