Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

update trigger 1

Status
Not open for further replies.

jamert

Programmer
Joined
Dec 9, 2007
Messages
80
Location
CA
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
 
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
 
SQLSister, hows does this compare then:

Code:
--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
 
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
 
Thanks for your valuable insight!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top