SQL Question

Should I use the template from MS SQL Management Studio to create new triggers?

If you create a new trigger in MS SQL Management Studio by using the GUI, it gives you this template:

--====================================
-- Create database trigger template
--====================================
USE <database_name, sysname, AdventureWorks>
GO

IF EXISTS(
SELECT *
FROM sys.triggers
WHERE name = N'<trigger_name, sysname, table_alter_drop_safety>'
AND parent_class_desc = N'DATABASE'
)
DROP TRIGGER <trigger_name, sysname, table_alter_drop_safety> ON DATABASE
GO

CREATE TRIGGER <trigger_name, sysname, table_alter_drop_safety> ON DATABASE
FOR <data_definition_statements, , DROP_TABLE, ALTER_TABLE>
AS
IF IS_MEMBER ('db_owner') = 0
BEGIN
PRINT 'You must ask your DBA to drop or alter tables!'
ROLLBACK TRANSACTION
END
GO


Should I use this template?

I dont know anything about triggers, but I think I need to use them. The purpose in this case is that on an insert to the table, I need to update one of the fields.

Please help me get started!

Answer

OK to begin with that is the wrong template if you want an ordinary trigger that one is a trigger on making structural changes to the table itself.

If you decide to do a trigger that affects data (as opposed to structure), there are several things you need to know. First and by far the most critical, triggers operate on sets of data not one row at time. You must write any trigger to handle multiple row inserts.updates or deletes. If you end up with any code setting the value in inserted or deleted to a variable, there is a 99% chance it will not work properly if multiple records are involved.

What is inserted or deleted you ask? That is the next thing you need to know about triggers, there are two pseudotables (inserted and deleted) that are only available in a trigger (or an output clause) which contain the new information being inserted or the updated values (in the inserted table) and the old information being deleted or being changed by an update (in the deleted table). So an insert has values in inserted, a delete has values in deleted and an update has values in both. Use these in your trigger to pull the values you need to change.