Creating Triggers in SQL-Server

You can create a trigger with SQL Server Management Studio (SSMS) .
To use the Enterprise Manager to create triggers:

1. Right-click the name of the table that the trigger will be assigned to.
2. Choose the All Tasks | Manage Triggers menu option.
3. Enter the appropriate Transact-SQL statements in the Trigger Properties dialog box, shown below:

Expansion of the previous trigger that enforces a specific business rule for the "employees" table. The business rule we’ll enforce is:
🧠 Business Rule:
(*) Prevent any employee from being assigned a salary greater than $250,000.
✅ Completed T-SQL Trigger:

CREATE TRIGGER trg_EnforceSalaryCap
ON employees
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Check for salary cap violation on INSERT or UPDATE
    IF EXISTS (
        SELECT 1
        FROM inserted
        WHERE salary > 250000
    )
    BEGIN
        RAISERROR('Salary cannot exceed $250,000.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- Optional: Handle DELETE event, if needed
    -- (In this case, no business rule on DELETE, but included for completeness)
END;

📝 Explanation:

• `SET NOCOUNT ON`: Prevents extra result sets from interfering with application logic.
• `inserted`: A virtual table representing new or updated rows.
• `RAISERROR`: Generates a custom error message with severity level 16 (user error).
• `ROLLBACK TRANSACTION`: Cancels the DML operation if the rule is violated.
• The DELETE case is included in the trigger, but no specific logic is added—this can be customized later.

A trigger can be fi red for any combination of insert, update, or delete events. Triggers are created and modifi ed with the standard DDL commands, CREATE, ALTER, and DROP, as follows:

CREATE TRIGGER Schema.TriggerName ON Schema.TableName
AFTER | INSTEAD OF [Insert, Update, (and or) Delete]
AS Trigger Code;

You can also create, view and modify triggers using Management Studio's Object Explorer, as shown in Figure 4-5.

While SQL Server Management Studio (SSMS) provides a graphical user interface for many database administration tasks, including creating and modifying triggers, the actual definition and logic of the trigger are specified using Transact-SQL (T-SQL) statements within the SSMS query editor.
Here's why this is the case:

• Granular Control: Triggers often involve complex business logic, conditional execution, and specific actions based on data changes. T-SQL offers the necessary control flow statements (like `IF`, `ELSE`), data manipulation language (DML), and system functions to implement this logic precisely.
• Defining Trigger Options: Options like `FOR INSERT`, `FOR UPDATE`, `FOR DELETE`, `WITH ENCRYPTION`, `NOT FOR REPLICATION`, and the trigger code itself are all part of the T-SQL `CREATE TRIGGER` statement. SSMS provides the framework to execute this statement.
• No Drag-and-Drop Logic: Unlike some visual programming tools, the core logic of database objects like triggers is defined through code. SSMS helps you write, execute, and manage this code.

Think of SSMS as the environment and the T-SQL as the language you use within that environment to define the trigger's behavior. You might use the SSMS object explorer to navigate to the table where you want to create a trigger and then right-click to select "New Trigger," but the window that opens will be a query editor where you write the T-SQL code for the trigger. So, while SSMS simplifies the process of interacting with the database and managing objects, the power and flexibility to define the specific behavior of a trigger lie in the T-SQL you write.
It's interesting how even with graphical tools like SSMS, the underlying power and flexibility for database objects like triggers come from writing code in T-SQL. It allows for a level of precision that a purely visual interface might not be able to offer.

• Creating triggers with Transact-SQL
  To create a trigger with Transact-SQL, you will use the CREATE TRIGGER Transact-SQL statement. The syntax for this statement is shown in the following series of images.