Home » SQL & PL/SQL » SQL & PL/SQL » create trigger (10.2.0.1,Windows)
create trigger [message #323793] Thu, 29 May 2008 08:33 Go to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Hi all,

I need to create trigger to 2 tables so that when table1 is either added or updated I have to insert a record into a new table and when table2 is added I have to insert a record

Please give me your suggestions.

Thank you.

Re: create trigger [message #323794 is a reply to message #323793] Thu, 29 May 2008 08:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The question is not clear. Please clarify.

is it

table 1   ---|
             |----> new table
table 2   ---|



or is it

table 1  <----> table 2


Also, post what you already tried and a test case.
Re: create trigger [message #323796 is a reply to message #323793] Thu, 29 May 2008 08:54 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Thomas,

Thanks for your reply. Below is what I need to do:

I’ve created a new table called ‘newtab’

1. When table1 is inserted then
•Execute trigger and insert a record into newtab

2. When table1 is updated then
•Execute trigger and update the existing record into newtab

3. When table2 is inserted then
•Execute trigger and insert a record into newtab

Please help. THanks much.
Re: create trigger [message #323798 is a reply to message #323796] Thu, 29 May 2008 09:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then create a before/after insert or update trigger on table1 and a before/after insert trigger on table2 that does the required inserting in newtab.

Both with the "for each row" clause.

Create Trigger statement

Further Examples
Re: create trigger [message #323803 is a reply to message #323793] Thu, 29 May 2008 09:24 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Thanks, Thomas.

Below are the triggers I tried:

I tried this for the first trigger:

CREATE or replace TRIGGER table1_trg
after insert ON table1
BEGIN
Insert into newtab (column1,column2....etc)
values(….);
Commit;
END table1_trg;
/

Below is for the second trigger:


CREATE or replace TRIGGER tabletab1_trg
after update ON table1
BEGIN
Insert into newtab (column1,column2....etc)
values(….);
Commit;
END tabletab1_trg;
/

But this trigger is supposed to update the existing record into the newtab table, not sure how I can do that...

below is for the third trigger:

CREATE or replace TRIGGER table2_trg
after insert ON table2
BEGIN
Insert into newtab (column1,column2....etc)
values(….);
Commit;
END table2_trg;
/

Please help me. Thanks
Re: create trigger [message #323805 is a reply to message #323803] Thu, 29 May 2008 09:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1. You don't have the "for each row" clause in your triggers. You need it if you want to fire an action for each inserted or updated row.

2. In the update trigger just replace

Insert into newtab (column1,column2....etc)
values(..);


with

update newtab
   set column2 = :new.column2,
       column3 = :new.column3
where column1 = :new.column1;


That is ASSUMING that column1 is the column the row can be uniquely identified between the two tables.

3. You shouldn't commit inside triggers.
Re: create trigger [message #323806 is a reply to message #323803] Thu, 29 May 2008 09:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, the first error is that you can't put commits in triggers.
Other than that, you'll have to use SHOW ERRORS or SELECT * FROM USER_ERRORS WHERE NAME = <...> and tell us what the problems are.
Re: create trigger [message #323807 is a reply to message #323803] Thu, 29 May 2008 09:35 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Try using a 'Update' statement instead of 'Insert' in your trigger.
CREATE or replace TRIGGER tabletab1_trg
after update ON table1
BEGIN
Update into newtab (column1,column2....etc)
values(….)
where newtab.coulmn1=:new.tabletab1_trg.commoncolumn_Name
Commit;
END tabletab1_trg;

[Updated on: Thu, 29 May 2008 09:36]

Report message to a moderator

Re: create trigger [message #323812 is a reply to message #323793] Thu, 29 May 2008 10:16 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Just a clarification, the newtab and the table1 does not have same number of columns and there are a few conditions for the values to be inserted:

The row to be inserted in newtab is -
acct = table1.sold
Name= table3.instituition
Okey= ‘OTHER’
Ind= table1.ind
stopsv = ‘N’ if table1’s active flag is set to ‘A’
else set to ‘Y’ if active flag is set to ‘I’


CREATE or replace TRIGGER table1_trg
after insert ON table1
BEGIN
Insert into newtab (acct,name,okey,ind,stopsv)
values(table1.sold, ??????, ‘other’, table1.ind, ??? );
END table1_trg;
/


IS it possible to get a value for one column from a different table?? also, the value for stopsv is conditional, is it possible to specify a condition?

THanks much
Re: create trigger [message #323819 is a reply to message #323793] Thu, 29 May 2008 10:34 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
I tried this, but didnt work:

CREATE or replace TRIGGER table1_trg
after insert ON table1
BEGIN
Insert into newtab (acct,name,okey,ind,stopsv)
values(:new.sold,select name from table3 where instituition='...' , ‘other’, :new.ind,
decode(:new.stopsv,'A','N','I','Y' );
END table1_trg;
/

Any suggestions? thank you
Re: create trigger [message #323821 is a reply to message #323819] Thu, 29 May 2008 10:39 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Yes.
If you refer the link provided by ThomasG 'Further Examples',everything is there.
Re: create trigger [message #323822 is a reply to message #323793] Thu, 29 May 2008 10:45 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Ok. THanks.

Is it possible to have more than 1 trigger for a table?

CREATE OR REPLACE TRIGGER table1_trg
before insert or update on table1
for each row
begin

:new.sold:= nvl(:new.sold,' ');
:new.stopsv:= nvl(:new.stopsv,' ');
:new.ind:= nvl(:new.ind,' ');
:new.special:= nvl(:new.special,' ');
:new.phone:= nvl(:new.phone,' ');
End;
/

I'll have to modify this and create the below as well:

CREATE or replace TRIGGER table1_trg
after insert ON table1
BEGIN
Insert into newtab (acct,name,okey,ind,stopsv)
values(:new.sold,select name from table3 where instituition='...' , ‘other’, :new.ind,
decode(:new.stopsv,'A','N','I','Y' );
END table1_trg;
/

THanks
Re: create trigger [message #323826 is a reply to message #323822] Thu, 29 May 2008 10:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
>Is it possible to have more than 1 trigger for a table?
Yes

Regarding your syntax, you can NOT use a select inside a values clause of the insert.

Either change the insert to use a SELECT instead of the VALUES, or replace the select with a variable that you populate BEFORE the insert.

Read up on the basic INSERT statement.

Re: create trigger [message #323828 is a reply to message #323793] Thu, 29 May 2008 11:01 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Okay. Thanks, THomas.

I tried this.

CREATE OR REPLACE TRIGGER table1_trg
AFTER INSERT ON table1
DECLARE
v_name VARCHAR2(30) ;
BEGIN
SELECT name INTO v_name
FROM table3 WHERE instituition='...' ;
INSERT INTO newtab (acct,name,okey,ind,stopsv)
VALUES (:new.sold,v_name , ‘other’, :new.ind,DECODE(:new.stopsv,'A','N','I','Y' )) ;
END table1_trg;

Re: create trigger [message #323829 is a reply to message #323828] Thu, 29 May 2008 11:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And does it work?

If now what's the problem?

The only thing I see at the moment is that the "for each row" is missing again.
Re: create trigger [message #323839 is a reply to message #323807] Thu, 29 May 2008 12:49 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
jramya wrote on Thu, 29 May 2008 10:35
Try using a 'Update' statement instead of 'Insert' in your trigger.
CREATE or replace TRIGGER tabletab1_trg
after update ON table1
BEGIN
Update into newtab (column1,column2....etc)
values(….)
where newtab.coulmn1=:new.tabletab1_trg.commoncolumn_Name
Commit;
END tabletab1_trg;



Is this some new Oracle syntax in 11g or something?
Re: create trigger [message #323973 is a reply to message #323839] Fri, 30 May 2008 03:25 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Embarassed Ooopssss...
Typo error,Actually meant to say replace the insert statement completely with an update statement.
Sorry,Must have been like
Update
newtab set column1=value1
<where clause>

Regards

Re: create trigger [message #324679 is a reply to message #323793] Tue, 03 June 2008 12:24 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Hi experts,

I am facing a new problem now. I've created a new table called gofileroom with fields acctno,order_key,firm_name,mkt_ind,stopsvc.

Condition for the Trigger:

• When a record is inserted into SLCST then execute the trigger and insert a record into the GoFileRoom table as follows:

1. AcctNo = slcst.sold_to_cust
2. Firm_name = Institution from the name/address table[namad] tied to the sold_to_cust by address code
3. Order_key = ‘0THER’
4. Mkt_ind = slcst.customer_group
5. StopSvc = ‘ ’

I created this trigger for the above condition,

create or replace trigger slcst_trg
after insert on slcst
for each row
declare
v_name varchar2(30);
begin
select namad.institution into v_name from namad,slcst where namad.address_code=slcst.address_code;
insert into gofileroom (acctno,firm_name,order_key,mkt_ind,stopsvc)
values (:new.sold_to_cust,v_name,'0ther', :new.customer_group,' ');
end slcst_trg;
/

When I insert a record into SLCST, getting the below error:

ORA-04091: table SLCST is mutating, trigger/function may not see it
ORA-06512: at SLCST_TRG
ORA-04088: error during execution of trigger 'SLCST_TRG'

Not sure why I am getting error while inserting record into SLCST because I created the trigger only after insert on SLCST.

Please help. Thanks.


Re: create trigger [message #324682 is a reply to message #323793] Tue, 03 June 2008 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
why do you consistently & repeatedly ignore posting guidelines?
http://www.orafaq.com/forum/t/88153/0/

Are both GOOGLE & the SEARCH function of this forum broken for you?

[Updated on: Tue, 03 June 2008 12:32] by Moderator

Report message to a moderator

Re: create trigger [message #324684 is a reply to message #323793] Tue, 03 June 2008 12:39 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Hi experts,

I am facing a new problem now. I've created a new table called gofileroom with fields acctno,order_key,firm_name,mkt_ind,stopsvc.

Condition for the Trigger:

• When a record is inserted into SLCST then execute the trigger and insert a record into the GoFileRoom table as follows:

1. AcctNo = slcst.sold_to_cust
2. Firm_name = Institution from the name/address table[namad] tied to the sold_to_cust by address code
3. Order_key = ‘0THER’
4. Mkt_ind = slcst.customer_group
5. StopSvc = ‘ ’

I created this trigger for the above condition,

 
create or replace trigger slcst_trg
after insert on slcst
for each row
declare
v_name varchar2(30);
begin
select namad.institution into v_name from namad,slcst where namad.address_code=slcst.address_code;
insert into gofileroom (acctno,firm_name,order_key,mkt_ind,stopsvc)
values (:new.sold_to_cust,v_name,'0ther', :new.customer_group,' ');
end slcst_trg;
/


When I insert a record into SLCST, getting the below error:

ORA-04091: table SLCST is mutating, trigger/function may not see it
ORA-06512: at SLCST_TRG
ORA-04088: error during execution of trigger 'SLCST_TRG'

Not sure why I am getting error while inserting record into SLCST because I created the trigger only after insert on SLCST.

Please help. Thanks.
Re: create trigger [message #324688 is a reply to message #324684] Tue, 03 June 2008 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't select or modify the table you are currently modifying.

Regards
Michel
Re: create trigger [message #324690 is a reply to message #323793] Tue, 03 June 2008 12:58 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Thanks,Michel. Is there any other way to get the value for institution without doing select on slcst table??

THank you very much.
Re: create trigger [message #324692 is a reply to message #324690] Tue, 03 June 2008 13:01 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Why are you even using that table in the join as it is the other table where you are getting the value from? I would also expect a TOO MANY ROWS exception to occur.

[Updated on: Tue, 03 June 2008 13:03]

Report message to a moderator

Re: create trigger [message #324695 is a reply to message #324692] Tue, 03 June 2008 13:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Is there any other way to get the value for institution without doing select on slcst table??



Yes. The normal way used in the dozen of examples already linked to.


Re: create trigger [message #324698 is a reply to message #323793] Tue, 03 June 2008 13:28 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
I thought that is the only way I can relate the address code in both namad and slcst tables..


The firm_name = Institution from the name/address table[namad]
tied to SLCST by address code.


create or replace trigger slcst_trg
after insert on slcst
for each row
declare
v_name varchar2(30);
begin
select institution into v_name from namad where namad.address_code=:new.address_code;
insert into gofileroom (acctno,firm_name,order_key,mkt_ind,stopsvc)
values (:new.sold_to_cust,v_name,'0ther', :new.customer_group,' ');
end slcst_trg;
/



I changed the select statement a little bit to new:address_code (instead of a join with slcst table). But still I am getting the below error when I insert record into slcst:

ORA-01403: no data found
ORA-06512: at SLCST_TRG
ORA-04088: error during execution of trigger 'SLCST_TRG'


I am very new in creating complicated triggers. Sorry for my ignorance. Thanks for all your help.
Re: create trigger [message #324704 is a reply to message #323793] Tue, 03 June 2008 13:47 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
By the way, you can do both an insert and update trigger in the same code.


if UPDATING then
......
END IF;

IF inserting THEN
.....
END IF;
Re: create trigger [message #324716 is a reply to message #323793] Tue, 03 June 2008 14:28 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Oh Okay, Thanks Bill.

I've merged both insert and update conditions:

CREATE OR REPLACE TRIGGER PUBS.slcstinsert_trg
after insert or update ON PUBS.SLCST for each row
declare
v_name varchar2(30);

begin
if inserting then
select institution into v_name from namad where namad.address_code=:new.address_code;
insert into gofileroom (acctno,firm_name,order_key,mkt_ind,stopsvc)
values (:new.sold_to_cust,v_name,'0ther', :new.customer_group,' ');
end if;

if updating then
select institution into v_name from namad where namad.address_code=:new.address_code;
update gofileroom
set firm_name= v_name,
mkt_ind=:new.customer_group,
stopsvc=DECODE(:new.active_inactive,'X','I',' ')
where acctno=:new.sold_to_cust;
end if;

end slcst_trg;
/


In the update trigger, the gofileroom table should be updated based on the slcst.sold_to_cust (acctno=slcst.sold_to_cust).
Not sure if I can use where acctno = :new.sold_to_cust in the update clause.

Also, the stopsvc should be 'X' if slcst.active_inactive is set to 'I' else it should be ' '. I have used this for this condition:

stopsvc=DECODE(:new.active_inactive,'X','I',' ')


Any advise, will be very helpful. Thank you,all.
Re: create trigger [message #324719 is a reply to message #324698] Tue, 03 June 2008 15:26 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
[quote title=plshelp wrote on Tue, 03 June 2008 14:28]
insert into gofileroom (acctno,firm_name,order_key,mkt_ind,stopsvc)
values (:new.sold_to_cust,v_name,'0ther', :new.customer_group,' ');
/


Just a little note here, putting blanks in a column is poor foresight as you will not know whether there is a NULL or any number of blank spaces in it by looking at output.

Quote:

ORA-01403: no data found
ORA-06512: at SLCST_TRG
ORA-04088: error during execution of trigger 'SLCST_TRG'

I am very new in creating complicated triggers. Sorry for my ignorance. Thanks for all your help.


This is not related to trigger code, it's just plain PL/SQL exception handling.
Re: create trigger [message #324720 is a reply to message #323793] Tue, 03 June 2008 15:35 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Thanks for your reply, Joy_division. I managed to create the insert trigger and it works fine now.

I still need to create the update trigger.. I mentioned in my previous post. Please help me if you can. THanks.
Re: create trigger [message #324722 is a reply to message #324720] Tue, 03 June 2008 15:40 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
plshelp wrote on Tue, 03 June 2008 16:35

I still need to create the update trigger.. I mentioned in my previous post. Please help me if you can. THanks.


You would use
:old
values for your update.
Re: create trigger [message #324723 is a reply to message #323793] Tue, 03 June 2008 15:49 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Okay. Thanks.

But how is it possible to have an if statement for stopsvc condition?

Also, the stopsvc should be 'X' if slcst.active_inactive is set to 'I' else it should be ' '.

I tried this, but it doesn't work.
stopsvc=DECODE(:new.active_inactive,'X','I',' ')


Below is the trigger I tried just to get the update trigger work, but no luck.

CREATE OR REPLACE TRIGGER PUBS.slcstupdate_trg
after update ON PUBS.SLCST for each row
declare
v_name varchar2(30);
begin

select institution into v_name from namad where namad.address_code=:new.address_code;
update gofileroom
set firm_name= v_name,
mkt_ind=:new.customer_group,
stopsvc=DECODE(:new.active_inactive,'X','I',' ')
where acctno=:old.sold_to_cust;
end slcst_trg;
/


For the stopsvc, I think I need to use if clause, if 'I' then set 'X' else ' '. But not sure how I can merge this condition with the other set commands in the update trigger.

Any help is appreciated. Thanks much.
Re: create trigger [message #324725 is a reply to message #323793] Tue, 03 June 2008 15:54 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>it doesn't work.
Use valid syntax!
Re: create trigger [message #324726 is a reply to message #323793] Tue, 03 June 2008 15:58 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
What is the exact error that you are getting. after the compile, type "show errors" to display any problems.
Re: create trigger [message #324729 is a reply to message #323793] Tue, 03 June 2008 16:05 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Bill, I am not getting any error, when I compile the trigger.

And it works fine for the firm_name, mkt_ind updates.

But it doesn't work for stopsvc update. Which should be updated to soemthing like this.

'X' if slcst.active_inactive is set to 'I' else it should be ' '.

My colleague asked me to try DECODE function, it didn't work.
I am thinking may be DECODE function is used for inserting record.

To update the stopsvc to 'X' or ' ', I need to mention it in the trigger, may be with an if clause?? Not sure. Please help.

Thank you.
Re: create trigger [message #324740 is a reply to message #323793] Tue, 03 June 2008 19:13 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Hi experts,

I am still struggling to find a way to update the stopsvc field in gofileroom table when the active_inactive field is updated in slcst.

This is the condition for the update trigger:

stopsvc should be updated to 'X' if slcst.active_inactive is set to 'I' else stopsvc should be ' '.

I tried to change different things and doesnt seem to work..

CREATE OR REPLACE TRIGGER PUBS.slcstupdate_trg
after update ON PUBS.SLCST for each row
declare
v_name varchar2(30);
begin

select institution into v_name from namad where namad.address_code=:new.address_code;
update gofileroom
set firm_name= v_name,
mkt_ind=:new.customer_group
where acctno=:old.sold_to_cust;
if new.active_inactive='I'
then stopsvc='X' 
elsif stopsvc=' '
end if;
end slcst_trg;
/


Please advise on any suggestions you may have. Thanks a lot.
Re: create trigger [message #324742 is a reply to message #323793] Tue, 03 June 2008 20:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do the correct actions in the correct order.
Re: create trigger [message #324889 is a reply to message #324740] Wed, 04 June 2008 07:54 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
plshelp wrote on Tue, 03 June 2008 20:13


CREATE OR REPLACE TRIGGER PUBS.slcstupdate_trg
after update ON PUBS.SLCST for each row
declare
v_name varchar2(30);
begin

select institution into v_name from namad where namad.address_code=:new.address_code;
update gofileroom
set firm_name= v_name,
mkt_ind=:new.customer_group
where acctno=:old.sold_to_cust;
if new.active_inactive='I'
then stopsvc='X' 
elsif stopsvc=' '
end if;
end slcst_trg;
/





This is full of invalid syntax and has moot code (and invalid at that).

[Updated on: Wed, 04 June 2008 07:55]

Report message to a moderator

Re: create trigger [message #324955 is a reply to message #323793] Wed, 04 June 2008 11:04 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Okay. I have corrected that. Thanks for all your help.

I am creating about 10 different triggers, so its driving me crazy here.. I am trying to find out how I can convert the value from numeric to alphanumeric.

This is the condition for the trigger:

WHen ORD is inserted then execute the trigger and insert a record into gofileroom table as follows:

Read the slcst where ord.sold_to=slcst.sold_to_cust and select address_code
Read the namad where address_code = address_code selected above
Set AcctNo = The sold to Customer on this order just inserted [ord.sold_to]]
Set Firm_name = Institution from the name/address table[namad] tied to the sold to by address code
Set Order_key = Convert the order reference no 9 Num [ord.reference_no] to 9 A/N
Set Mkt_ind = Sold To Customer New field Market Indicator [slcst.mkt_ind]
Set StopSvc = ‘X ’ if Sold To Customer’s active/inactive flag is set to ‘I’ [slcst.active_inactive]
Else set it to ‘ ‘


CREATE OR REPLACE TRIGGER ordinsert_trg
after insert ON ord for each row
declare
acc_name varchar2(5);
v_name varchar2(30);
act_name varchar2(3);
c_name varchar2(4);
begin
select address_code into acc_name from slcst where slcst.sold_to_cust = :new.sold_to; 
select namad.institution into v_name from namad,slcst where namad.address_code= acc_name;
select customer_group into c_name from slcst where slcst.sold_to_cust= :new.sold_to;
select active_inactive into act_name from slcst where slcst.sold_to_cust= :new.sold_to;
insert into gofileroom (acctno,firm_name,order_key,mkt_ind)
values (:new.sold_to,v_name,:new.reference_no, c_name);
if act_name = 'I' then
insert into gofileroom (stopsvc) values ('X');
else insert into gofileroom (stopsvc) values (' ');
end if;
end ordinsert_trg;
/


the order_key column in gofileroom is varchar2(9) and reference_no in ord is number(9). So when I insert 6 in ord, it should insert 6 Alphanumeric in gofileroom.

Will I be able to do it with convert()?? Please give me your suggestions. Thanks.

Re: create trigger [message #324956 is a reply to message #324955] Wed, 04 June 2008 11:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
I am creating about 10 different triggers, so its driving me crazy here.


Which is quite normal, since triggers were never intended to manage all the application logic.

When you need that many triggers then it is a sure sign that the data/application design itself is heavily flawed.

And convert() as nothing to do with number/character conversions, it's for converting a character string from one character set to another.

You have to use to_char() or to_number()
Re: create trigger [message #324959 is a reply to message #324955] Wed, 04 June 2008 11:23 Go to previous messageGo to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ever thought of selecting multiple columns, instead of four times selecting a single column?

What part of the documentation made you decide that 'may be DECODE function is used for inserting record.'?

Also, you do realize that you insert two individual rows, right?

and you have no join condition on "namad,slcst" in your second select.

[Updated on: Wed, 04 June 2008 11:25]

Report message to a moderator

Previous Topic: Getting the exact elapsed time of a SQL
Next Topic: View validity
Goto Forum:
  


Current Time: Fri Dec 09 03:59:18 CST 2016

Total time taken to generate the page: 0.17189 seconds