Solved

Oracle Trigger to compare between current month and previous

Posted on 2012-03-21
3
393 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
Comment Utility
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
Comment Utility
I don't need to write a query or view I need a trigger
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now