CODEWITHSUNDEEP
sql-servert-sql
MohsenFM
MohsenFM

Reputation: 129

Finding out which rows are inserted , updated or deleted using triggers

I have a table named indication in Database and it has three columns Name, Age, and Enable.

I want to create a trigger that inserts an alarm into my alarm table whenever the Age is under 18 and Enable is true, I want to check the record on the indication table at the exact moment that it has been inserted, that way I can check whether it should be inserted in alarm or not.

I found COLUMNS_UPDATED (Transact-SQL) on MSDN and it works for updated columns, is there the same thing for ROWS_UPDATED?

Upvotes: 3

Views: 2076

Answers (2)

Jaques
Jaques

Reputation: 2287

Firstly I think you have to increase your knowledge on the way triggers work, and what the different type of triggers are.

You can create a trigger like this

CREATE TRIGGER trg_Indication_Ins 
   ON  Indication 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    Insert Alarms (column1, column2) Select value1, value2 from inserted where Age < 18 and Enable = 1
END

This should basically do what you are looking for, and from what I understand from your quesion.

UPDATE:

Basically triggers can fire on INSERT, UPDATE or DELETE or any combination of the three, You can also set it to fire 'FOR/AFTER' the event (of which both actually means AFTER), or INSTEAD OF the event. A trigger will always have "internal" or meta-tables on the event.

These tables are inserted and deleted

The inserted table is basically all the new records that is applied to the table, and the deleted table have all the records that will be removed. In the case of the UPDATE event, the inserted table will have all the new values and deleted will have all the old values.

The inserted table will be empty on a DELETE trigger, and the deleted table will be empty on an INSERT trigger

Triggers can affect performance drastically if not used properly, so use it wisely.

Upvotes: -1

ErikE
ErikE

Reputation: 50271

You can always set your trigger to respond to only an INSERT action, with

CREATE TRIGGER TR_Whatever_I ON dbo.YourTable FOR INSERT
AS
... (body of trigger)

Be aware FOR INSERT is the same as AFTER INSERT. You also have the option of INSTEAD OF, but with that you have to perform the data modification yourself. There is no BEFORE trigger in SQL Server.

In some cases it is very convenient to handle more than one action at once because the script for the different actions is similar--why write three triggers when you can write just one? So in the case where your trigger looks more like this:

CREATE TRIGGER TR_Whatever_IUD ON dbo.YourTable FOR INSERT, UPDATE, DELETE
AS
... (body of trigger)

Then you don't automatically know it was an insert in the body. In this case, you can detect whether it's an insert similar to this:

IF EXISTS (SELECT * FROM Inserted)
AND NOT EXISTS (SELECT * FROM Deleted) BEGIN
   --It's an INSERT.
END

Or, if you want to determine which of the three DML operations it is:

DECLARE @DataOperation char(1);
SET @DataOperation =
   CASE
      WHEN NOT EXISTS (SELECT * FROM Inserted) THEN 'D'
      WHEN NOT EXISTS (SELECT * FROM Deleted) THEN 'I'
      ELSE 'U'
   END
;

Triggers still run if a DML operation affects no rows (for example, UPDATE dbo.YourTable SET Column = '' WHERE 1 = 0). In this case, you can't tell whether it was an update, delete, or insert--but since no modification occurred, it doesn't matter.

A Special Note

It's worth mentioning that in SQL Server, triggers fire once per operation, NOT once per row. This means that the Inserted and Deleted meta-tables will have as many rows in them during trigger execution as there are rows affected by the operation. Be careful and don't write triggers that assume there will only be one row.

Upvotes: 5

Related Questions