Solved

Oracle Trigger to compare between current month and previous

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 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

22 Experts available now in Live!

Get 1:1 Help Now