ansaurus

Question

Answer 1

+1 A:

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.

Randolph Potter 2010-07-29 02:07:30

Yes, I am familiar with triggers on MySQL but not on MSSQL, so I thought there is a different way of implementation.Thanks!

johnshaddad 2010-07-29 02:10:10

But how can I know which row is being updated? Say I want to know the Primary Key of that row, how to do that? Something like to send an email after a new item is added, 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)

johnshaddad 2010-07-29 02:14:39

mrdenny's comment in reply to Joel Coehoorn's answer is a good summary.

Randolph Potter 2010-07-29 03:03:38

Answer 2

A:

The documentation is always a good place to look.

Joel Coehoorn 2010-07-29 02:12:39

But how can I know which row is being updated? Say I want to know the Primary Key of that row, how to do that? Something like to send an email after a new item is added, 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)

johnshaddad 2010-07-29 02:19:24

When you reference a trigger you don't reference the actual table. There are two virtual tables called "inserted" and "deleted" which you use. Inserted has the new rows (or new values when an update has happened) while deleted has the deleted rows (or the old values when an update has happened).

mrdenny 2010-07-29 02:31:06

Answer 3

+1 A:

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

Thomas 2010-07-29 02:30:25

And then to call that procedure from the trigger? Is that what you mean? But isn't the sql mailing system procedure enough to handle that in the trigger? or should I create a procedure that calls that system procedure, and then call this procedure in the trigger?

johnshaddad 2010-07-29 02:35:13

@johnshaddad - No. I would not do email operations from a trigger. Instead, I would have your calling code use the stored proc instead of operating against the table directly.

Thomas 2010-07-29 02:38:52

When you said "There could be multiple rows inserted." doesn't that fire the trigger multiple times? Or how? Could you explain how I would loop in such a case?

johnshaddad 2010-07-29 02:46:58

@johnshaddad - A trigger fires once per statement or operation. For example, suppose you have a query `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`).

Thomas 2010-07-29 03:38:58

@johnshaddad - Now, if instead of a single `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.

Thomas 2010-07-29 03:40:38

Thanks! I took that into consideration and created a cursor in my trigger, which calls the procedure for sending the corresponding notification message for each row in "INSERTED" table. Working like a charm xD

johnshaddad 2010-07-29 03:46:25

Answer 4

+1 A:

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

mrdenny 2010-07-29 02:32:27

ansaurus

tags:

views:

answers:

Create Trigger in SQL Server

related questions