Home » SQL & PL/SQL » SQL & PL/SQL » coding inside the trigger (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
coding inside the trigger [message #589532] Mon, 08 July 2013 05:58 Go to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member


Hi All,

I have the below code and its working fine also.But suppose the more different type of status column added in the test_alert table then
inside the trigger the codes also added with the same way.Is that right approach to the coding inside the trigger .Please suggest me.

create table test_alert(job_name varchar2(20),mail_status char(1) default 'N',txn_status char(1) default 'N');

INSERT into test_alert values('PRESIDENT','N','Y');
INSERT into test_alert values('MANAGER','Y','Y');
INSERT into test_alert values('SALESMAN','N','N');
INSERT into test_alert values('CLERK','Y','Y');
INSERT into test_alert values('ANALYST','N','N');

create table emp1 as select * from emp;

alter table emp1 add (mail_status varchar2(20),txn_status varchar2(20));

set define off;
CREATE OR REPLACE TRIGGER emp1_trig
before update on emp1 
FOR EACH ROW
begin
  begin 
  select decode(mail_status,'Y','NO NEED','SENT') ,decode(txn_status,'Y','PENDING','PROCESSED') into :new.mail_status,:new.TXN_status
  FROM test_alert where job_name=:old.job;
  exception 
  when no_data_found then 
  :new.mail_status:='SENT';
  :new.TXN_status:='PROCESSED';
  end;
end;

update emp1 set sal=sal+100;

commit;

select * from emp1;


Regards,
Nathan
Re: coding inside the trigger [message #589533 is a reply to message #589532] Mon, 08 July 2013 06:09 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hi,

What is exactly your requirement in terms of txn_status and txn_status upon DMLs on your table? What's the rule?

Regards,
Dariyoosh
Re: coding inside the trigger [message #589535 is a reply to message #589533] Mon, 08 July 2013 06:42 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It depends on what you plan to do with new statuses. The way you put it now, anything but 'Y' in both cases defaults to SENT (mail_status) and PROCESSED (txn_status). If that's OK for new statuses that might appear, no problem. If not, you'll have to rewrite the trigger. Alternatively, create a mapping table (or tables) which would say which status means what, and then - within a trigger - select status' description according to its status.

(By the way, the last UPDATE statement is doubtful as it modifies ALL records in the EMP1 table. Did you do that intentionally?)
Re: coding inside the trigger [message #589538 is a reply to message #589533] Mon, 08 July 2013 07:13 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Dariyoosh, Thanks for responding.

The test_alert table holds the mail_status and txn_status columns what the job opted for. For example MANAGER has interested for both emails and transaction alert by choosing mail_status as 'Y' and txn_status as 'Y'.

So first we kept the transactions as 'PENDING' and mail as 'NOT SENT'. And later after sending an email to the respective customer we will mark that status to 'SENT' and 'PROCESSED' respectively.

In fact the above code should be like this inside the trigger..
set define off;
CREATE OR REPLACE TRIGGER emp1_trig
before update OF SAL on emp1 
FOR EACH ROW
begin
  begin 
  select decode(mail_status,'Y','NOT SENT','SENT') ,decode(txn_status,'Y','PENDING','PROCESSED') into :new.mail_status,:new.TXN_status
  FROM test_alert where job_name=:old.job;
  exception 
  when no_data_found then 
  :new.mail_status:='SENT';--not bothered
  :new.TXN_status:='PROCESSED';--not bothered
  end;
end;



LittleFoot,

Yes I intentionally did it.The mapping part is not needed as of now.Any default value is fine.

In similar way the selection list may increases to get data from different table if required.So the question is inside a trigger if we put lots of select statement will harm anything in the execution or it should be avoided.

Regards,
Nathan
Re: coding inside the trigger [message #589539 is a reply to message #589538] Mon, 08 July 2013 07:23 Go to previous messageGo to next message
Bill B
Messages: 1108
Registered: December 2004
Senior Member
since the trigger is on emp1, the following code from your trigger is NOT allowed

update emp1 set sal=sal+100;

commit;
Re: coding inside the trigger [message #589541 is a reply to message #589539] Mon, 08 July 2013 07:30 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Bill B,

I am not getting You exactly.I think you took the trigger wrongly,Actually the update statement is out side to trigger.So it is working fine.
Later I changed the trigger for only to sal column.Anyway can the trigger also treated as a procedure in terms of accessing so many tables.

Regards,
Nathan
Re: coding inside the trigger [message #589542 is a reply to message #589541] Mon, 08 July 2013 07:35 Go to previous message
Bill B
Messages: 1108
Registered: December 2004
Senior Member
as long as you do not commit inside the trigger and you do not do anything that would modify/insert/select any other record in the triggered table you can access any number of different tables, assuming you do not have circular triggers for example a trigger on tablea that alters tableb which has a trigger which alters tablea.
Previous Topic: Numbers to Alphabets
Next Topic: Reading File with UL_FILE
Goto Forum:
  


Current Time: Wed Oct 01 11:27:28 CDT 2014

Total time taken to generate the page: 0.08854 seconds