How to create a trigger

Last modified: October 1, 2024

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or upadte of data. It is a database object which is bound to a table and is executed automatically.

Prerequisites

You should have the following privileges:

• TRIGGER privilege on the proper database
• SELECT, INSERT, ALTER, UPDATE, SELECT and DELETE privileges on the proper table(s).

Create a trigger

1. In Database Explorer make sure that connection to your server containing the base is active.
2. Navigate to the node of a certain table and expand it.
3. Right-click the Triggers node and choose New Trigger from the shortcut menu. A trigger editor document will appear.
4. Input name of the trigger and type a script that will represent the trigger.
5. Save the document by clicking the Update Database button on the bottom of the window, or the Save button on the Standard toolbar. If there are any errors in the SQL syntax, you will be notified about it.

CREATE TRIGGER syntax

CREATE TRIGGER dEmployee ON HumanResources.Employee 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;
 
    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;
 
    SET NOCOUNT ON;
 
    BEGIN
        RAISERROR 
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
            10, -- Severity.
            1); -- State.
 
         -- Rollback any active or uncommittable transactions
         IF @@TRANCOUNT > 0
         BEGIN
             ROLLBACK TRANSACTION;
         END
     END;
END;

Create a trigger with the help of snippets

To simplify the process of a Trigger creation, you may use the CreateTrigger snippet.

1. Click the New SQL button on the Standard toolbar.
2. Right-click anywhere in the SQL document, and then click Insert Snippet. The snippets list will appear.
3. Double-click the CreateTrigger snippet.

4. The following code will be inserted to the document.

5. Edit the code accordingly and save the trigger. If there are any errors in the SQL syntax, you will be notified about it.

Edit a trigger

Edit a view by selecting Edit Trigger from the Database Explorer node shortcut menu. In the opened document, you can alter the query text. Save the document to apply changes you’ve made to the query text.

Drop a trigger

To drop a trigger, click Delete on the Database Explorer node shortcut menu.

Enable and disable triggers

To enable or disable a trigger, click Enable or Disable on the Database Explorer node shortcut menu.

To enable or disable all triggers at once, select Enable All or Disable ALL from the Database Explorer node shortcut menu.