introduction to triggers in sql server

Question

if you create a new trigger in sql server, 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. on an insert i will need to update one of the fields. please help me get started!

Answer 1

Since you don't know anything about triggers, I would say no, don't use the template.

Read the books online page for Create Trigger and write the trigger by hand.

There is probably more in that template code than you actually need. Read the manual and keep it simple.

Answer 2

If you don't know anything about triggers then I would strongly suggest that you read up on them before implementing them. Get Triggers right and they can make your life a lot easier; get it wrong and Triggers will cause you a lot of trouble.

I would suggest starting off with this tutorial

http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

You can use the above SQL as a template or you can simply write your own. I would suggest you write your own as you'll understand what you are doing. Obviously only do this after you have done some serious reading on triggers. Check out MSDN too

Answer 3

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 strucutre), 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 muliple records are involved.

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