Oracle Trigger

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.
egovernmentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NikolasGCommented:
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
egovernmentAuthor Commented:
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
Geert GOracle dbaCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

egovernmentAuthor Commented:
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
NikolasGCommented:
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
MikeOM_DBACommented:
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
NikolasGCommented:
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
Geert GOracle dbaCommented:
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
egovernmentAuthor Commented:
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
Geert GOracle dbaCommented:
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
slightwv (䄆 Netminder) Commented:
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
egovernmentAuthor Commented:
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
egovernmentAuthor Commented:
And the above trigger is invalid what the reason ?
0
MikeOM_DBACommented:

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
egovernmentAuthor Commented:
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
Geert GOracle dbaCommented:
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
egovernmentAuthor Commented:
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
egovernmentAuthor Commented:
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
Geert GOracle dbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.