I got lost when I wanted to create trigger using the pre-defined "CREATE TRIGGER" of SQL Server 2008 R2. Could you please give me a direct SQL statement that I can use to create a trigger, and tell me how to define AFTER, BEFORE, and all that?
Also, how can I know the rows UPDATED/INSERTED/DELETED, and use their column values to do operations inside the trigger?
The basic syntax is
CREATE TRIGGER YourTriggerName ON dbo.YourTable
FOR|AFTER INSERT, UPDATE, DELETE
AS
BEGIN
/*Put what ever you want here*/
UPDATE AnotherTable
SET SomeColumn = AnotherColumn
FROM inserted | deleted
END
GO
Databases are set-oriented and triggers are no different. A trigger will fire when a given operation is performed and that operation might affect multiple rows. Thus, the question "Say I want to know the Primary Key of that row" is a misnomer. There could be multiple rows inserted.
SQL Server provides two special tables for AFTER triggers named inserted and deleted which represent the rows that were inserted or deleted by an action and are structured identically to the table being affected. An update trigger might populate both inserted and deleted whereas an insert trigger would only populate the inserted table.
From comments:
but the email recipient will be decided based on a value in a second table, where the foreign key ID is located in the first table (which is the one with trigger
The answer to this question is to use the inserted table (which again, you must assume could have multiple rows) to cycle through the rows and send an email. However, I would recommend against putting email logic in a trigger. Instead, I would recommend putting that logic in a stored procedure and send your email from that.
For reference: Create Trigger
Insert Table(...) Select ... From OtherTable that inserts 100 rows. The trigger will only fire once. The special inserted table will have 100 rows that you can cycle on using something like a cursor or can use in other SQL statements (e.g. Insert LogTable(...) Select ... From inserted).Insert Table(...) Select ... From OtherTable call, suppose you execute 100 Insert statement where you insert one row each time. Each statement will fire the trigger once and the inserted table will contain a single row each time it fires. However, when writing a trigger you must assume that someone could insert, update or delete to the table using a set operation which will affect multiple rows.A trigger is an event-based process that is "triggered" after a table is changed in some way. This will be on DELETE, UPDATE, INSERT, and so forth. Your BEFORE and AFTER syntax will define whether to run the trigger before or after the event is committed.
That's the short version. Check out MSDN.