CODEWITHSUNDEEP
sqltriggers
Stuart
Stuart

Reputation: 496

SQL Trigger to update row

I need a SQL trigger that would zero pad a cell whenever its inserted or updated. Was curious if its best practice to append two strings together like I'm doing in the update command. Is this be best way to do it?

CREATE TRIGGER PadColumnTenCharsInserted ON Table
AFTER INSERT
AS

DECLARE
    @pad_characters VARCHAR(10),
    @target_column NVARCHAR(255)

SET @pad_characters = '0000000000'
SET @target_column = 'IndexField1'

IF UPDATE(IndexField1)
BEGIN
    UPDATE Table
    SET IndexField1 = RIGHT(@pad_characters + IndexField1, 10)
END

GO

Upvotes: 0

Views: 1124

Answers (2)

Joe McCarthy
Joe McCarthy

Reputation: 57

Two questions:

  1. What are you doing with @target_column? You declare it and set it with a column name, but then you never use it. If you intend to use the variable in your subsequent SQL statements, you may need to wrap the statements in an EXECUTE() or use sp_executesql().

  2. The syntax "UPDATE Table..." is OK for your update statement assuming that "Table" is the name of the table you are updating. What seems to be missing is a filter of some kind. Or did you really intend for that column to be updated for every row in the whole table?
    One way to handle this would be to declare another variable and set it with the PK of the row that is updated, then use a where clause to limit the update to just that row. Something like this:

    DECLARE @id int

    SELECT @id = Record_ID FROM INSERTED

    -- body of your trigger here

    WHERE Record_ID = @id

  3. I like your padding code. It looks good to me.

Upvotes: 0

John Pick
John Pick

Reputation: 5650

Your padding code looks fine.

Instead of updating every row in the table like this:

    UPDATE Table

update just the row that triggered the trigger:

    UPDATE updated

Also, you've still got some extraneous code -- everything involving @target_column. And it looks like you're not sure if this is an INSERT trigger or an UPDATE trigger. I see AFTER INSERT and IF UPDATE.

Upvotes: 1

Related Questions