T-SQL Trigger After Specific Insertion

Question

I need to write a trigger that only runs when an insertion that has a certain field with a specific value is inserted. There is no updating.

For example, if I insert an entry that has column X with the value "MAC ID", I want it to run a trigger, but ONLY if the value is "MAC ID".

I know how to test for this normally, but not sure how to test this in a trigger. So far I've found that I want to use "FOR INSERT", but beyond that I don't know how to implement it.

Any help is much appreciated! Thanks!

Answer 1

You need to be aware that triggers run once for a batch, not once per row. As such, you may be running in a circumstance in which some of the rows match your criteria, and others do not.

As such, you'd do best to write your trigger logic to select the matching rows from inserted. If there were no matching rows, the rest of your logic will be working with an empty set, but that's no real problem - SQL Server is plenty fast enough at doing nothing, when required.

E.g. if you're inserting these rows into an audit table, something like:

create trigger MacId
on T
for insert
as
    insert into Audit(Col1,Col2,Col3)
    select i.Col1,i.Col2,'inserted'
    from inserted i
    where i.Col4 = 'MAC ID'

Answer 2

create trigger MaxIdInsert
on YourTable
after insert

as

    if exists
    (
        select *
        from inserted
        where ColumnX = 'MAX ID'
    )
    begin
        -- do what you want here if ColumnX has value of 'MAX ID'
    end

go

There's no way to only fire a trigger on certain DML specifications (besides insert, update, and/or delete). Your best bet is to test out the dynamic inserted table, which contains records that are inserted into YourTable. In this, you can test for inserted records that have a ColumnX value of "MAX ID".

Edit: In case you were wondering, I know you specified a for trigger in your question. A for trigger is equivalent to an after trigger in SQL Server.