trigger on field update

Question

Post reply

trigger on field update

Terry-380449

Default port

Points: 1458
More actions
July 17, 2007 at 1:54 am

#177447

Hello

I need to do a trigger on my table, whenever a field is not null and its value is updated, i want to change the value of another field

do i have to use power and the column number????? isnt it an easier way??

tx!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 1

SwePeso

SSC-Dedicated

Points: 39725

July 17, 2007 at 3:00 am

#719930

something like this. it is readable in Books Online.

TRIGGER for UPDATE

IF COLUMN(col4)

BEGIN

END

N 56°04'39.16"
E 12°55'05.25"

mdouglass Default port Points: 1412 More actions · Answer 2

Hi Terry,

I have these triggers on by customer table based upon what people type uin to correctly format the data, it may help you!

INSERT

CREATE TRIGGER TR_Insert_tblAddress ON dbo.tblAddress

FOR INSERT

AS

BEGIN

UPDATE tblAddress SET

account_holder = CASE LEN(RTRIM(LTRIM(UPPER(ISNULL(I.account_number_sterling, ''))))) + LEN(RTRIM(LTRIM(UPPER(ISNULL(I.account_number_euro, ''))))) + LEN(RTRIM(LTRIM(UPPER(ISNULL(I.account_number_usdollar, ''))))) WHEN 0 Then 0 Else 1 END,

account_number_sterling = RTRIM(LTRIM(UPPER(ISNULL(I.account_number_sterling, '')))),

account_number_euro = RTRIM(LTRIM(UPPER(ISNULL(I.account_number_euro, '')))),

account_number_usdollar = RTRIM(LTRIM(UPPER(ISNULL(I.account_number_usdollar, '')))),

company_name = RTRIM(LTRIM(UPPER(REPLACE(REPLACE(I.company_name, ',', ' '), '.', ' ')))),

address1 = RTRIM(LTRIM(UPPER(REPLACE(REPLACE(I.address1, ',', ' '), '.', ' ')))),

address2 = RTRIM(LTRIM(UPPER(REPLACE(REPLACE(I.address2, ',', ' '), '.', ' ')))),

address3 = RTRIM(LTRIM(UPPER(REPLACE(REPLACE(I.address3, ',', ' '), '.', ' ')))),

city = RTRIM(LTRIM(UPPER(REPLACE(REPLACE(I.city, ',', ' '), '.', ' ')))),

state = RTRIM(LTRIM(UPPER(REPLACE(REPLACE(I.state, ',', ' '), '.', ' ')))),

post_code = RTRIM(LTRIM(UPPER(REPLACE(REPLACE(I.post_code, ',', ' '), '.', ' ')))),

web_address = RTRIM(LTRIM(LOWER(REPLACE(I.web_address, 'http://', ' ')))),

telephone = RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(I.telephone, '-', ' '), ')', ''), '(', ''), '+', ''))),

facsimile = RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(I.facsimile, '-', ' '), ')', ''), '(', ''), '+', ''))),

education = I.education

FROM tblAddress AS T INNER JOIN Inserted AS I

ON T.address_id = I.address_id

END

UPDATE

CREATE TRIGGER TR_Update_tblAddress ON dbo.tblAddress

FOR UPDATE