Solved

Oracle Trigger

Posted on 2012-03-14
20
666 Views
Last Modified: 2012-03-20
I want to write Oracle Trigger to perform the following: -

Checking a new added records in a table with other table records and print the result in text file.
0
Comment
Question by:egovernment
  • 8
  • 6
  • 3
  • +2
20 Comments
 
LVL 6

Expert Comment

by:NikolasG
ID: 37718641
Hi,
You can't handle files within oracle triggers.
What you can do is populate an results table from where you can after take your results through plsql in a text or excel file.
What kind of checks you want the trigger to make, what results do you need?
Give more info in order to help you.

Please feed back.
0
 

Author Comment

by:egovernment
ID: 37718672
Hi

My Idea is
I need an Oracle trigger to check data in table added new with other data in other table and tell me if there any different.

For example
Employee Salaries

One table using to insert Employee salaries for current month and other table using to store the Employee Allowances and Deductions

So I need to compare first table with second table for the employee and if there any different should alarm me.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 37718755
you need to find who changed data ?

use audit for insert, update, delete on that table
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
0
 

Author Comment

by:egovernment
ID: 37718905
you need to find who changed data ?

Not like that

Still our application is new and we put the salaries in table and other table generate the salaries and stored there so we need to compare between them that is not to find who changed data.
0
 
LVL 6

Expert Comment

by:NikolasG
ID: 37718971
So
from what you say
here is a thing that does what you say
I create a trigger that checks if the employ has no previous salary like the one that you try to insert now and if not it insert the employee id, the salary and the date in the table called zsalarycheck,
I have inside how to create the table zsalarycheck and a sequence in order to have a key.

You can take the report with a simple select for you employee, date or salary
You can modify the trigger to have more data by just changing the insert and the table zsalarycheck

Hope that  helps.

create or replace trigger Employess_salaries
before insert on employee
for each row
declare
checksal number;

begin
select count(*) into checksal  from emploee e where e.empid=:new.empid and e.salary=:new.salary
if checksal=0 then 
insert into zsalarycheck(id,cdate,empid,salary) values (zsalarycheck_seq.nextval,sysdate,:new.empid,:new.salary)
end if;
end



create table zsalarycheck
(
  ID      NUMBER(10) not null,
  cdate       date,
  empid      NUMBER(10) not null,
  salary     float,
)

create sequence zsalarycheck_SEQ
minvalue 1
maxvalue 2147483647
start with 1
increment by 1
cache 10
cycle;

Open in new window

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37721729
Unfortunately the trigger posted by: NikolasG will produce the "mutating table" error ORA-04091. In a trigger you cannot select from the same table that will execute the trigger.

@egovernment: Also, the requirements you stated are too fuzzy to be able to provide any significant solution. Please clarify what you need to do using an example or even "pseudo-code".
:p
0
 
LVL 6

Expert Comment

by:NikolasG
ID: 37724038
MikeOM_DBA seem to be right...
I didn't notice that when i was writing the code.  :D
The way to override is a bit more complicated...
Please give more info in order to find the most suitable solution.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 37724057
egovernment, you need a table for the changes ?
like a delta stock table ?

employee  new_salary old_salary dt_changed  osuser program
X         120        110        05/03/2012  XXX    YYY
Y         200        180        05/03/2012  XXX    YYY

Open in new window

0
 

Author Comment

by:egovernment
ID: 37724624
Ok I want to explain what I need (Exactly I change the scope of my question after I read the expert answers)

I have 1 tables as the following: -
Table_A (Each month in this table added the salaries of employees)
======
Emp_Id
Alw_Ded_Id
Alw_Ded_Amount
From_Period
To_Period

Open in new window


What I need exatly compare same emplyee salary for current month with last month he get salary there and give me as report if this employee salary change using fields
Alw_Ded_Id

Open in new window

and
Alw_Ded_Amount

Open in new window

for current month and previous by using field
From_Period

Open in new window

with
To_Period

Open in new window

and what new things in Alw_Ded_Id added or removed in current month.

This trigger should be work after inserted data in Table_A
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 37725022
if you add (insert) the data in the same table every month then you don't need a trigger
if you want to see the changes a select with a order by should suffice

select * from table_a
where emp_id = :employee_number
order by datecolumn desc
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37725932
To add the the select suggestion above, create two select statements, one for this month and one just like it for last month.  Union them together.  If you only get a single row back for an employee, nothing changed (the union removes duplicate rows).

You can also make this a monthly script, schedule it with dbms_scheduler and even have the database email you the report.
0
 

Author Comment

by:egovernment
ID: 37733137
I try to using a trigger over this table
tbl_EmpTest

Open in new window


The trigger is
CREATE OR REPLACE TRIGGER TRI_Emp
AFTER DELETE OR INSERT OR UPDATE
ON TBL_EmpTest
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
/******************************************************************************
   NAME:       
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        17/03/2012             1. Created this trigger.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     
      Sysdate:         17/03/2012
      Date and Time:   17/03/2012, 11:24:45 ¿, and 17/03/2012 11:24:45 ¿
      Username:         (set in TOAD Options, Proc Templates)
      Table Name:       (set in the "New PL/SQL Object" dialog)
      Trigger Options:  (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
   tmpVar := 0;

   SELECT MySeq.NEXTVAL INTO tmpVar FROM dual;
   :NEW.SequenceColumn := tmpVar;
   :NEW.CreatedDate := SYSDATE;
   :NEW.CreatedUser := USER;
   
   update tbl_EmpTest
   set Emp_Id = 50
   where
   Emp_Id = 100;
   
   COMMIT;   

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;   
END ;

Open in new window


When I add a value inside tbl_EmpTest I get an error
ORA-04098

Open in new window


I looking to write trigger to after insert records give me the result in new table
0
 

Author Comment

by:egovernment
ID: 37733197
And the above trigger is invalid what the reason ?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37735625

ORA-04098: trigger 'string.string' is invalid and failed re-validation
Cause:       A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
Action:       Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.
Check out that the trigger has no errors before trying to execute.
0
 

Author Comment

by:egovernment
ID: 37736489
Hi MikeOM_DBA

I make compile the trigger and I get this error
TRIGGER MOJ_PRD.TRI_BADR
On line:  26
PLS-00049: bad bind variable 'NEW.SEQUENCECOLUMN'

Open in new window

0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 37736524
you don't put a commit in a trigger

new is a reference to the table being used
if you don't have the sequencecolumn in your table then that will give the error

you may not need the sequence ...
are you using sequences ?

the sample you are using is the default sample from toad
> it's not customized for your environment ... it's just a sample
0
 

Author Comment

by:egovernment
ID: 37736558
Now I modify the above trigger as the following: -

CREATE OR REPLACE TRIGGER TRI_EMP
AFTER DELETE OR INSERT OR UPDATE
ON TBL_EMPTEST 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
/******************************************************************************
   NAME:       
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        17/03/2012             1. Created this trigger.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     
      Sysdate:         17/03/2012
      Date and Time:   17/03/2012, 11:24:45 Õ, and 17/03/2012 11:24:45 Õ
      Username:         (set in TOAD Options, Proc Templates)
      Table Name:       (set in the "New PL/SQL Object" dialog)
      Trigger Options:  (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
   tmpVar := 0;

/*   SELECT MySeq.NEXTVAL INTO tmpVar FROM dual;
   :NEW.SequenceColumn := tmpVar;
   :NEW.CreatedDate := SYSDATE;
   :NEW.CreatedUser := USER;*/
   
   update TBL_EMPRESULT
   set fld_EmpId = 50
   where
   fld_EmpId not in (select emp_id from tbl_EMPTEST where emp_id = 50);
   
   COMMIT;

/*   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;*/   
END ;

Open in new window


But when I execute the trigger I get an error

ORA-04091: ÌÏæá TBL_EMPTEST íÊÛíÑ¡ ÞÏ áÇ íÔÚÑ Èå ÇáÒäÇÏ Ãæ ÇáæÙíÝÉ
ORA-06512: ÚäÏ "TRI_EMP", line 30
ORA-04088: ÙåÑ ÎØÇ ÃËäÇÁ ÊäÝíÐ ÇáÒäÇÏ TRI_EMP'

Open in new window

0
 

Author Comment

by:egovernment
ID: 37736571
What the problem here ?

   update TBL_EMPRESULT
   set fld_EmpId = 50
   where
   fld_EmpId not in (select emp_id from tbl_EMPTEST where emp_id = 50);
   
   COMMIT;

Open in new window

0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 37736579
You absolutely need to read Basic SQL
or follow a course for Basic SQL.
Such a course will give you the basic knowledge you need to create a procedure/trigger

do not put a commit in the trigger !
do i have to repeat myself ?

tbl_emptest is in a modifying state inside the trigger

if you want to change fld_EmpId for emp_id inside the trigger then use this:

CREATE OR REPLACE TRIGGER TRI_EMP
AFTER DELETE OR INSERT OR UPDATE
ON TBL_EMPTEST 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  :NEW.CreatedDate := SYSDATE;
  :NEW.CreatedUser := USER;*/
  
  IF :NEW.emp_id = 50 THEN
    update TBL_EMPRESULT
    set fld_EmpId = 50
    where fld_EmpId not in (:NEW.emp_id);
  END IF; 
END ;

Open in new window


what is your goal ?
the trigger code makes no sense at all
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 37736586
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

708 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

17 Experts available now in Live!

Get 1:1 Help Now