SQL Trigger - help with fundamentals

I rarely write triggers. I can use h开发者_如何学Pythonelp setting up the thing.

CREATE TRIGGER audit_tableName
ON dbo.tableNameAudit
AFTER CREATE, UPDATE, DELETE on tableName
AS
BEGIN
  --Get Created,updated,deletes items
  INSERT into dbo.tableNameAudit(columns) VALUES ([allCUDitems])

END
GO

How do I get an iterate any CREATED, UPDATED, DELETED items that caused the trigger?

check out this msdn article.

the short of it are there are 2 special tables, inserted and deleted, that are accessible in your trigger. inserted will contain updated rows and inserted rows, while deleted will contain updated rows and deleted rows.

make sure that you understand that for a batch operation a trigger is only fired one time, so be sure to handle the possibility of multiple rows being present in those tables.

Triggers have access to two pseudo tables: INSERTED and DELETED. As their name suggests, these pseudo tables will contain all the values added or removed from the table. An update will cause a row in each of the pseudo-tables. The structure of these pseudo-tables is identical with the structure of the table on which the table is declared.

In addition the UPDATE() function inside a trigger will return TRUE for columns that were updated.

You can use INSERTED.[COLUMN] OR

SELECT [COLUMN] 
FROM INSERTED

This on SQL Server.

Hope it helps.