SQL Trigger for ONLY currently inserted row

Question

I have a Trigger, MyTrigger, I want to make that ONLY changes the DESCRIPTION FIELD, [Description], of the recently inserted row in TABLEA. As shown, I have the basics of it but it will currently will just scan the entire table for every INSERT on TABLEA.

TRIGGER IS BELOW.............

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER MyTrigger  
   ON  TABLEA
   AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;

UPDATE TABLEA
SET [DESCRIPTION] = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([DESCRIPTION], '…' , '...'),'–','-'),'’',''''),'½','1/2'),'•','+'),'”','"'),'“','"'),'‘',''''),'—','-'),'é','e')
WHERE  DESCRIPTION LIKE '%…%' 
    OR DESCRIPTION LIKE '%–%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%½%'
    OR DESCRIPTION LIKE '%•%'
    OR DESCRIPTION LIKE '%”%'
    OR DESCRIPTION LIKE '%“%'
    OR DESCRIPTION LIKE '%‘%'
    OR DESCRIPTION LIKE '%—%'
    OR DESCRIPTION LIKE '%é%'

END
GO

I guessed at the database type. Please make sure you include that in your tags when you post. (Use the edit link right below the tags to correct if that was not what you use.) — Mat, May 17 '12 at 19:27
Read about [how to create a trigger](http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx) and make sure to read the part about the `Inserted` logical table that's available to you inside your trigger code - it will contain exactly those rows that have been inserted. .... — marc_s, May 17 '12 at 19:29
Very closely related to [another post](http://stackoverflow.com/questions/1837475/sql-insert-trigger-to-update-inserted-table-values) that updates the record(s) that was/were just inserted — aingram, May 17 '12 at 19:32
As an aside, the following should be equivalent to your entire series of `DESCRIPTION LIKE …`: `DESCRIPTION LIKE '%[…–’½•”“‘—é]%'`. — Andriy M, May 17 '12 at 19:51
While your following marc_s' suggestion, also note that triggers work on rowsets, i.e. sometimes users alter more than a single row. — HABO, May 17 '12 at 20:15

score 2 · Answer 1 · answered May 17 '12 at 20:21

I would suggest doing this in an INSTEAD OF trigger, thus only actually performing one DDL action against the table (you can also ignore the WHERE clause here and just apply the replace calls to everything in inserted).

DROP TRIGGER MyTrigger ON TABLEA;
GO

CREATE TRIGGER dbo.MyTrigger  
   ON  TABLEA
   INSTEAD OF INSERT
AS 
BEGIN
  SET NOCOUNT ON;

  INSERT TABLEA(col1, col2, ..., Description)
    SELECT col1, col2, ...,
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE([DESCRIPTION], '…' , '...'),'–','-'),'’','''')
    ,'½','1/2'),'•','+'),'”','"'),'“','"'),'‘',''''),'—','-'),'é','e')
    FROM inserted;
END
GO

score 1 · Answer 2 · answered May 17 '12 at 19:27

1

why you are not using tables like INSERTED AND DELETED rather than scanning whole table and updating last record ....

check this : Use the inserted and deleted Tables

answered May 17 '12 at 19:27

Pranay Rana

175,020
35
237
263

They're actually more *logical* or *conceptual* rather than *magic* ;-) – marc_s May 17 '12 at 19:29

pete · Answer 3 · 2012-05-17T19:43:30.490

I would use the inserted table and make the trigger INSTEAD OF not AFTER (example follows).

SET NOCOUNT ON;

DECLARE     @temp       table   --temp table that can be modified
(
    Column1
    , Column1
    , Column1
    , [DESCRIPTION]     --this table should mimic your TABLEA schema
)

INSERT INTO @temp
SELECT      *
FROM        inserted

UPDATE @temp
SET [DESCRIPTION] = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([DESCRIPTION], '…' , '...'),'–','-'),'’',''''),'½','1/2'),'•','+'),'”','"'),'“','"'),'‘',''''),'—','-'),'é','e')
WHERE  DESCRIPTION LIKE '%…%' 
    OR DESCRIPTION LIKE '%–%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%½%'
    OR DESCRIPTION LIKE '%•%'
    OR DESCRIPTION LIKE '%”%'
    OR DESCRIPTION LIKE '%“%'
    OR DESCRIPTION LIKE '%‘%'
    OR DESCRIPTION LIKE '%—%'
    OR DESCRIPTION LIKE '%é%'


INSERT INTO TABLEA
            (
                Column1
                , Column1
                , Column1
                , [DESCRIPTION]
            )
SELECT      Column1
            , Column1
            , Column1
            , [DESCRIPTION]
FROM        @temp

END
GO

As far as I know, you cannot **update** the `Inserted` (or `Deleted`) logical tables - they are to be treated as read-only tables — marc_s, May 17 '12 at 19:34
rechecked the docs, you are correct. Updated code to use a temp table instead. — pete, May 17 '12 at 19:43
Don't think you really need a @temp table and an update. Why not just apply the replace during the insert? This seems like a lot of extra I/O for nothing... — Aaron Bertrand, May 17 '12 at 20:23

score 0 · Answer 4 · answered May 17 '12 at 19:40

Please refer to last line in code - change Identifier with your table's Unique identifier / (Primary Key ?).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER MyTrigger  
   ON  TABLEA
   AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;

UPDATE TABLEA
SET [DESCRIPTION] = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([DESCRIPTION], '…' , '...'),'–','-'),'’',''''),'½','1/2'),'•','+'),'”','"'),'“','"'),'‘',''''),'—','-'),'é','e')
WHERE  (DESCRIPTION LIKE '%…%' 
    OR DESCRIPTION LIKE '%–%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%’%'
    OR DESCRIPTION LIKE '%½%'
    OR DESCRIPTION LIKE '%•%'
    OR DESCRIPTION LIKE '%”%'
    OR DESCRIPTION LIKE '%“%'
    OR DESCRIPTION LIKE '%‘%'
    OR DESCRIPTION LIKE '%—%'
    OR DESCRIPTION LIKE '%é%')
    AND EXISTS (SELECT * FROM Inserted i WHERE i.Identifier = TableA.Identifier)

END
GO

SQL Trigger for ONLY currently inserted row

4 Answers4