garymarshallsa
asked on
sql trigger that updates file2 only if the field is changed from its original value on file 1
I have a working sql trigger below. It works well,but I need it to change file 2 only if ship_date in file 1 is changed from the original date. If other updates in file 1 are done but the ship_date is unchanged, I do not want the second file to be updated. The trigger currently updates file 2 any time a change is done in file 1, even if ship_date remains constant
create trigger tr_change_file_two
on Oeordfil for update
as
begin
update f2 SET date2 = i.ship_date,
date3 = i.ship_date
from Oeordinl f2 join inserted i on f2.ord_no= i.ord_no
end
create trigger tr_change_file_two
on Oeordfil for update
as
begin
update f2 SET date2 = i.ship_date,
date3 = i.ship_date
from Oeordinl f2 join inserted i on f2.ord_no= i.ord_no
end
Last Comment
ASKER
I changed the trigger as provided and it still updates the two fields even if the ship_date does not change. In a nutshell a page is provided to the user who updates some of the information on the screen. When they save they screen it changes the record to incorportate the changes.
Is there a way to compare the ship_date old and new and only invoke the change of the ship_date is different?
Is there a way to compare the ship_date old and new and only invoke the change of the ship_date is different?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
worked ?
ASKER
everything worked well with the join on the deleted along with the where statement. The update works as needed. thanks again
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
create trigger tr_change_file_two
on Oeordfil for update
as
begin
IF UPDATE(ship_date)
BEGIN
update f2 SET date2 = i.ship_date,
date3 = i.ship_date
from Oeordinl f2 join inserted i on f2.ord_no= i.ord_no
END
end