update trigger 1

[jamert]

Hi I was wondering when it is bad to use an update trigger on a table, example when the table has 5 million records with 10 columns.


Thanks

 

[SQLSister]

Triggers are usually used to enforce business rules. Poorly written triggers can cause performance issues, but usually a trigger cannot be gotten rid of because then the business rules will not be guaranteed to be followed. We have triggers on on much larger tables than those described. Often the larger the table the more important it is to enforce business rules at the table level rather than relying on all the possible ways the data in the table could be effected to enforce the rules.

However, if you are writing a trigger there is one thing you must be aware of. If multiple records are inserted or updated or deleted (which of those are applicable depends on the trigger of course), then the trigger must be able to handle them. In no case should you ever see a trigger that uses an insert with the values clause for instance! And if at all possible (and most of the time it is) do not write a loop or a cursor in a trigger.

"NOTHING is more important in a database than integrity." ESquared

 

[jamert]

SQLSister, hows does this compare then:

--create update trigger
CREATE TRIGGER update_HealthGoalsHistory
ON HealthGoals 
FOR update
AS
BEGIN
INSERT INTO HealthGoalsHistory 
(	del.HealthGoalID,
	del.GoalTypeID,
	del.OperatorID,
	del.Op1,
	del.Op2,
	del.StartDate
)
select HealthGoalID,
	GoalTypeID,
	OperatorID,
	Op1,
	Op2,
	StartDate
from deleted del
END

 

[SQLSister]

Looks fine. However if you are storing history, you might consider adding a column for the date and the user who made the change. You can also grab these easily enough in the trigger and they come in very handy when you need to research past changes.

"NOTHING is more important in a database than integrity." ESquared

 

[jamert]

Thanks for your valuable insight!