update question

[ktucci]

i have this update procedure below...i want to only update when the field when it is null or the same value...but if the field contains data different then the data being supplied in the update i would like to not update the field and write the data to a temp table to analize later...any help will be greatly appreciated

update temp_all_line_carrier
set U = dbo.temp_carrier_001.resale_code_id
FROM dbo.temp_20010331_02 INNER JOIN
dbo.temp_carrier_001 ON
dbo.temp_20010331_02.carrier = dbo.temp_carrier_001.RESALE_DESC
RIGHT OUTER JOIN
dbo.temp_all_line_carrier ON
dbo.temp_20010331_02.SD_LINE_ID = dbo.temp_all_line_carrier.line_number
where dbo.temp_20010331_02.type = 'U'

 

[redlam]

if you are using sql2000, you can use an 'instead of' trigger... i can give you more on that if it applies.
let me know...

 

[ktucci]

yes i am...but i am unfamiliar with an 'instead of' trigger

 

[redlam]

from bol:
INSTEAD OF triggers override the standard actions of the triggering statement (INSERT, UPDATE, or DELETE). For example, an INSTEAD OF trigger can be defined to perform error or value checking on one or more columns, and then perform additional actions before inserting the record. For instance, when the value being updated in an hourly wage column in a payroll table exceeds a specified value, a trigger can be defined to either produce an error message and roll back the transaction, or insert a new record into an audit log before inserting the record into the payroll table.

from me
you could write an instead of trigger like this using the system inserted and deleted tables:

create trigger tr_check_update on temp_all_line_carrier
instead of update
as
-- check values
if (select U from inserted) <> (select U from deleted) and (select U from deleted) is not null
-- send different values to audit table
begin
insert into audit_table
select * from inserted
end
else
-- update same or null values
begin
update temp_all_line_carrier
set U = (select U from inserted)
end

 

[ktucci]

i also have a sql 7 db that needs the same concept applied....is there another method i could use for the sql 7 since 'instead of' triggers are not supported...i am going to test instead of trigger now....thanks again for your help

keith

 

[redlam]

i'm going to defer to another techie for sql 7 - i'm sure you can do something similar using a trigger but it would probably require more code and a rollback... maybe tlbroadbent can help if he's out there. he might know of a more efficient method.
good luck.
michelle

 

[ktucci]

thanks again for your help