PDA

View Full Version : Changing values in inserting record via triggers

niras
01-22-02, 09:45
Hi

Does anyone of you know how I ca create a trigger to do the following

Table tbs contain 3 columns : total, num_col and alpha_col

I'm doing an insert in table tbs:
insert into tbs (total) values ('111aaa')

Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.

Can anyone help ?!?!?

:)
Torsten
alligatorsql.com
01-22-02, 16:35
Hi Torsten,

this is a soultion for Oracle:

BEGIN
select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ')) into FieldNum from dual;

select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
'1234567890 ')) into fieldChar from dual;

UPDATE table set fieldnumeric = TO_NUMBER(FieldNum),
fieldvarchar = fieldChar;
END;

Hope this helps ?

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
niras
01-24-02, 08:12
Originally posted by alligatorsql.com
Hi Torsten,

this is a soultion for Oracle:

BEGIN
select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ')) into FieldNum from dual;

select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
'1234567890 ')) into fieldChar from dual;

UPDATE table set fieldnumeric = TO_NUMBER(FieldNum),
fieldvarchar = fieldChar;
END;

Hope this helps ?

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Hi Manfred
Thanks for your answer.
What I need to do is on a SQL Server.
I'll try and see, if I can modify your suggestion, so it fits into a SQLServer environment. But I can't find a SQLServer thing like Oracles :new.field1. Do you know if theres a way to reference columns in the current record in a SQLServer trigger ?

Torsten
alligatorsql.com
01-24-02, 13:13
Hello Torsten,

there must be a way to manipulate the current triggerfields; otherwise its would make less sence to program a trigger.
Perhaps you try it in the MS Server forum. I havenīt got a documentation af that database, but it canīt be to difficult to figure this out ...

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
cslaughter
07-24-02, 02:42
You're going to need to reference the inserted object in SQLServer. Like :new.field in Oracle, you can reference inserted.field by using a join between your base table (table1) and inserted.

Select table1.field from inserted.fieldkey where table1.fieldkey=insert.fieldkey.

Hope this helps.

Chris