Operate SQL Server Triggers and Stored Procedures Safely

• Use triggers for narrow, row-change side effects
• Example trigger
• Why this trigger pattern helps
• Trigger notes
• Write stored procedures with explicit transaction and error handling
• Stored procedure template
• What each line is doing
  • create or alter procedure
  • set nocount on;
  • set xact_abort on;
  • declare @ErrorMessage nvarchar(max)
  • declare @RowsCreated bigint = 0
  • begin try / begin catch
  • begin transaction
  • set @RowsCreated = @@ROWCOUNT
  • commit transaction;
  • if @@trancount > 0 rollback transaction;
  • set @ErrorMessage = error_message();
  • throw;
• Why this stored procedure pattern is commonly used in production
• A simple operational design
• Final notes

SQL Server remains common in production stacks, especially where operational systems, scheduled loads, and reporting databases share the same platform. In those environments, two building blocks appear frequently:

• triggers for event-driven table behavior
• stored procedures for repeatable, transactional data work

The tricky part is not getting each feature to work once. The tricky part is making them predictable when data volume grows, failures happen, or the next engineer has to debug them at 2 AM.

Use triggers for narrow, row-change side effects

Triggers are used when logic must run automatically in response to insert, update, or delete activity. Typical use cases are narrow and local:

• audit logging
• lightweight status synchronization
• enforcing a business rule that must stay close to the table

Less suitable use cases include long-running or invisible workflow orchestration. Triggers can slow down writes, hide side effects, and become hard to reason about when they do too much.

Example trigger

create or alter trigger dbo.trg_orders_audit
on dbo.orders
after insert, update
as
begin
    set nocount on;

    insert into dbo.order_audit (
        order_id,
        audit_action,
        changed_at
    )
    select
        i.order_id,
        case
            when d.order_id is null then 'INSERT'
            else 'UPDATE'
        end as audit_action,
        sysdatetime()
    from inserted i
    left join deleted d
        on i.order_id = d.order_id;
end;
go

Why this trigger pattern helps

• it uses the inserted and deleted pseudo-tables correctly
• it stays set-based instead of assuming a single row
• it limits the trigger to an audit concern instead of embedding a large pipeline

Trigger notes

• always assume multiple rows can be inserted or updated at once
• keep trigger logic short and deterministic
• avoid calling remote systems or long-running procedures from a trigger
• document triggers clearly because they are easy to forget during debugging

Write stored procedures with explicit transaction and error handling

For data engineering work, stored procedures often sit at the center of controlled loads. They can:

• populate staging tables
• merge transformed rows into target tables
• log load metrics
• fail cleanly so partial writes do not stay committed

The main requirement is explicit handling of transaction boundaries and error behavior.

Stored procedure template

Below is the core template pattern. One detail to watch closely: SQL Server uses @@ROWCOUNT, not @@ORWCOUNT. The production example below uses the correct system function so the code is runnable.

create or alter procedure dbo.usp_load_customer_snapshot
as
begin
    set nocount on;
    set xact_abort on;

    declare @ErrorMessage nvarchar(max);
    declare @RowsCreated bigint = 0;

    begin try
        begin transaction;

        insert into dbo.customer_snapshot (
            customer_id,
            customer_name,
            snapshot_date
        )
        select
            c.customer_id,
            c.customer_name,
            cast(sysdatetime() as date)
        from dbo.customers c
        where c.is_active = 1;

        set @RowsCreated = @@ROWCOUNT;

        insert into dbo.etl_run_log (
            process_name,
            rows_created,
            completed_at
        )
        values (
            'usp_load_customer_snapshot',
            @RowsCreated,
            sysdatetime()
        );

        commit transaction;
    end try
    begin catch
        if @@trancount > 0
            rollback transaction;

        set @ErrorMessage = error_message();

        insert into dbo.etl_error_log (
            process_name,
            error_message,
            failed_at
        )
        values (
            'usp_load_customer_snapshot',
            @ErrorMessage,
            sysdatetime()
        );

        throw;
    end catch
end;
go

What each line is doing

create or alter procedure

This makes deployments easier because the procedure can be created if it does not exist yet, or updated in place if it already exists. It is much cleaner than separate drop and create steps.

set nocount on;

This suppresses row-count messages like (100 rows affected) from being returned to the client after each statement.

Why that matters:

• reduces noisy output
• avoids confusing job runners or client code that expects one clean result
• is a standard SQL Server procedure setting for operational code

set xact_abort on;

This tells SQL Server to automatically terminate and roll back the current transaction for many runtime errors.

Why that matters:

• reduces the chance of leaving a transaction partially open
• makes behavior more predictable for data loads
• pairs well with try/catch for controlled failure handling

In data engineering procedures, this is a common default.

declare @ErrorMessage nvarchar(max)

This variable stores the error text captured inside the catch block. That gives you something useful to log before rethrowing the error.

declare @RowsCreated bigint = 0

This tracks the number of rows inserted by the main load step. Capturing row counts is useful for:

• run logging
• alerting on unexpected volume drops
• debugging whether a job did real work

begin try / begin catch

This is the core SQL Server error-handling structure. Everything expected to succeed goes in the try block. Failures are handled in the catch block.

That gives you one place to:

• roll back open work
• capture the message
• log the failure
• rethrow the error for upstream tooling

begin transaction

This marks the start of the unit of work. If the procedure needs multiple writes to succeed together, they should happen inside one explicit transaction.

In this example:

• the snapshot insert
• the success log insert

are treated as one logical operation.

set @RowsCreated = @@ROWCOUNT

This captures the number of rows affected by the immediately preceding statement, which here is the insert into dbo.customer_snapshot.

This line is important in ETL-style procedures because it provides a lightweight metric without running another count(*).

Important note:

• @@ROWCOUNT must be read immediately after the statement you care about
• any later statement can change its value

commit transaction;

This finalizes the work only after all required statements succeed. Without this, the transaction would stay open and create locking or consistency problems.

if @@trancount > 0 rollback transaction;

Inside the catch block, this safely checks whether a transaction is still open before rolling it back.

That matters because:

• some failures may already terminate the transaction state
• rolling back only when needed avoids a second error during error handling

set @ErrorMessage = error_message();

This pulls the actual SQL Server error text into a variable so it can be persisted in an error log table or included in downstream diagnostics.

throw;

This rethrows the original error after cleanup and logging.

This is important because silent failure can hide operational problems. If SQL Agent runs this procedure, throw; ensures the job step still fails visibly instead of appearing to succeed.

Why this stored procedure pattern is commonly used in production

• transaction handling is explicit
• row counts are easy to log
• the failure path is just as deliberate as the success path

A simple operational design

For many SQL Server data workloads, a reliable pattern looks like this:

1. a source table changes or receives new rows
2. an optional trigger writes a small audit record
3. an orchestration layer calls a stored procedure
4. the stored procedure loads, logs, commits, or rolls back
5. monitoring checks the log tables for rows created and errors raised

That keeps each feature in the lane where it is strongest:

• triggers for immediate table-side behavior
• stored procedures for transactional load logic

Final notes

• avoid putting large ETL workflows directly inside triggers
• standardize one stored procedure template across the team so error handling is consistent
• log row counts and failures early because operational debugging gets easier fast