Solved

Oracle Trigger to compare between current month and previous

Posted on 2012-03-21
3
414 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

734 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