Wednesday, September 16, 2015

Column level triggers - Oracle

In this case I have  requirement where I need to update account_status column in same table with user status. When ever user deleted flag is 0

Lets create a table:

Create table users users (username varchar2(20)),fullname varchar2(30),account_status varchar2(10) default OPEN,deleted number(1), LOCK_DATE date);

Now insert some values:

insert into users values('ARVIND111','ARVIND KUMAR','',,''SYSDATE);
insert into users values('RAGHU111','RAGHU RAM','','',SYSDATE);
insert into users values('RAJ111','RAJ KUMAR','',''SYSDATE);
insert into users values('HARI111','HARI KRISHNA','',''SYSDATE);


Lets say you want to update a column with the account status 'OPEN' or 'LOCKED'. when ever we have a update in another column.

Sample :

In the below case, when ever we update deleted 0, we will update another column saying the account_status is open. If deleted =1, then the account_status is locked.

Lets create trigger now :

create or replace TRIGGER USER_LOCKDATE
BEFORE UPDATE OF DELETED ON users
FOR EACH ROW
BEGIN
  IF (:NEW.DELETED=0)
  then
  :NEW.ACCOUNT_STATUS := 'OPEN';
  ELSE
  :NEW.ACCOUNT_STATUS := 'LOCKED';
  :new.LOCK_DATE := SYSDATE;
  end if;
  END;

Note: 
Enable trigger by using. ( Alter trigger USER_LOCKDATE enable; )
Disable trigger by using. ( Alter trigger USER_LOCKDATE disable; )

Now with above trigger, when you update deleted column, it will update the account_status and lock_date.

No comments :

Post a Comment