Creating a Trigger

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation 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).

Creating a Trigger

1. In Database Explorer, make sure that the 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 select New Trigger on the shortcut menu. A trigger editor document will appear.

4. Input a name of the trigger and type a script that will represent the trigger.

5. Save the document by clicking the Update Database button at 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;

Creating a Trigger with the Help of Snippet

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

1. Click the New SQL button on the Devart - Main toolbar.

2. Right-click anywhere in the SQL document, and then click Insert Snippet. The snippets list will appear.

3. Double-click the CreateTrigger snppet.

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.

Editing a Trigger

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

Dropping a Trigger

Drop a trigger by selecting Delete from Database Explorer on the shortcut menu.

Enabling and Disabling Triggers

Enable or disable a trigger by selecting Enable or Disable from Database Explorer on the sgortcut menu.

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

• Previous
• Next