Home » SQL & PL/SQL » SQL & PL/SQL » Help on create trigger
Help on create trigger [message #199862] Thu, 26 October 2006 08:20 Go to next message
kennyy
Messages: 1
Registered: October 2006
Location: Perth
Junior Member
Hi, i'm new to sql

I would like to create a trigger to keep history of changes to the name, address and contact no of my customer. i also need to create a table to store the change, date of the change and the user-id of the person making the change.

Anyone can help me ? cheers
Re: Help on create trigger [message #199869 is a reply to message #199862] Thu, 26 October 2006 08:59 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> create table employees as select * from hr.employees;

Table created.

SQL> create table emp_hist as select * from employees where 1 = 2;

Table created.

SQL> create table usr_info ( usrname varchar2(20),ddate date);

Table created.
SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger usr_info
  2  after insert or update or delete
  3  on employees
  4  for each row
  5  begin
  6  insert into usr_info values ( user, sysdate);
  7* end;
SQL> /

Trigger created.

SQL> update employees
  2  set salary = 0000
  3  where rownum = 1;

1 row updated.

SQL> select * from usr_info;

USRNAME              DDATE
-------------------- ---------
SCOTT                26-OCT-06

SQL> create or replace trigger hist_emp
  2  after delete or update
  3  on employees
  4  for each row
  5  begin
  6  insert into emp_hist values
  7  (:old.employee_id,:old.first_name,:old.last_name,:old.email,
  8  :old.phone_number,:old.hire_date,:old.job_id,:old.salary,
  9  :old.commission_pct,:old.manager_id,:old.department_id);
 10  end;
 11  /

Trigger created.


SQL> delete employees
  2  where rownum = 1;

1 row deleted.

SQL> select count(*) from emp_hist;

  COUNT(*)
----------
         2

SQL> select count(*) from usr_info;

  COUNT(*)
----------
         2
Re: Help on create trigger [message #199870 is a reply to message #199862] Thu, 26 October 2006 09:06 Go to previous message
rikfair
Messages: 22
Registered: October 2006
Location: UK
Junior Member
Hi

The following link contains an article for generic audit tables that can be used on most tables.

http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11020

Hope it helps
Razz
Previous Topic: function is inserting multiple time
Next Topic: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process
Goto Forum:
  


Current Time: Mon Dec 05 18:59:49 CST 2016

Total time taken to generate the page: 0.22076 seconds