Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create Trigger in SQL Server

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?

like image 607
JoHa Avatar asked Jul 29 '10 02:07

JoHa


People also ask

How do I change the Code of a trigger in SQL?

Modifying the Code of a SQL Server Trigger. When you need to change the code of a trigger you can use any of the next methods. Use the ALTER TRIGGER statement. Drop and re-create the trigger. Use the CREATE OR ALTER statement (Only if your version of SQL Server is greater than SQL Server 2016)

How do I create a transaction trigger in SQL Server?

CREATE TRIGGER (Transact-SQL) Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement.

How do I create a trigger from an event?

First, to create a new trigger, you specify the name of the trigger and schema to which the trigger belongs in the CREATE TRIGGER clause: CREATE TRIGGER production.trg_product_audit. Code language: SQL (Structured Query Language) (sql) Next, you specify the name of the table, which the trigger will fire when an event occurs, in the ON clause: ...

Why do we need triggers in SQL?

One of the most used scenarios for triggers is to maintain a log of changes in a given table. In such case, there is no point on having three different triggers, each one firing on INSERT, UPDATE and DELETE operations.


2 Answers

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
like image 185
mrdenny Avatar answered Oct 21 '22 12:10

mrdenny


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

like image 30
Thomas Avatar answered Oct 21 '22 13:10

Thomas