compare values in SQL table

[Suzi167]

Hi ,

I have a trigger on a table in SQL Server that currenlty executes a stored procedure each time an UPDATE is done to the table.
Here is the body of the trigger:


CREATE TRIGGER EMPLOYEE_TRIGGER
ON dbo.Employees
AFTER UPDATE
AS
EXEC PROCEDURE_FOR_TRIGGERS'200','200','200'




I need to change it though to execute only if the Title column Changed its value. So I need to compare the new and the old values of the Title column in the table and I need the trigger to go through all the records in the table.

In Oracle the syntax is as follows:



IF :NEW.Title<> :OLD.Title THEN


PROCEDURE_FOR_TRIGGERS(:NEW.PROC_ID,:NEW.SAMPLE_ID,:NEW.Title);

END IF;





Is there a built in functionality like that in SQL Server or shold I use Temp Tables?

Thanks

Susan

[TheCPUWizard]

Best bet (performance) is to use different stored procedures. In general an all purpose UPDATE (or insert) is a less than ideal design. It is often better to have multiple procedures each based on the specific use case.

[Suzi167]

Thanks for the reply.

My issue here is not however the stored procedure.

I need to modify my trigger to execute that stored procedure only if the value in the Title Column hs changed.

What is the syntax to do that?
Also I am reading that the trigger will execute once per statement in SQL - how can I make it so that it executes for each of the rows?

Thanks

Susan

[TheCPUWizard]

Susan, my point is that you are taking an "unconventional" approach, that does not conform to most standard practices. Therefore there are no answers to your questions (the last one is impossible) given your current design.

[PeejAvery]

What about using a trigger that fires both before and after update? Then create temp tables with only the titles to compare both before and after. Then dispose of the temp table.

Susan, my point is that you are taking an "unconventional" approach, that does not conform to most standard practices.
I don't get how there can be an unconventional approach when no approach has been given. :confused:

I don't even think you understand her problem. She isn't trying to do "an all purpose UPDATE (or insert)", but rather have a triggered stored procedure to detect the before to after changes. This is completely conventional, since so many databases can be linked.

[Suzi167]

Thanks PeejAvery,

I like your suggestion. I was thinking something along the same lines.
I was not sure if there was something I can use out of the box with SQL as with Oracle.

I was reading that there are two temp. system tables "deleted" and "inserted" which seem to be similar to the :NEW and :OLD tables in Oracle.

Anyway I think I have a better understanding now.

Thanks

Susan