Oracle Trigger

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.

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.

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

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.

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;

Select allOpen in new window

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

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.

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

Select allOpen in new window

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

Select allOpen 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

Select allOpen in new window

and

Alw_Ded_Amount

Select allOpen in new window

for current month and previous by using field

From_Period

Select allOpen in new window

with

To_Period

Select allOpen 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

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

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.

I try to using a trigger over this table

tbl_EmpTest

Select allOpen 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 ;

Select allOpen in new window

When I add a value inside tbl_EmpTest I get an error

ORA-04098

Select allOpen in new window

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

And the above trigger is invalid what the reason ?

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.

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'

Select allOpen in new window

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

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 ;

Select allOpen 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'

Select allOpen in new window

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;

Select allOpen in new window

basic sql course:
http://databases.about.com/od/Basic-SQL-Tutorials/Basic-SQL-Tutorials.htm
http://www.sqlcourse.com/select.html
http://it.toolbox.com/blogs/oracle-guide/introduction-to-basic-sql-part-1-5456