USE Sandbox
GO

CREATE TABLE [my_table] (
    [id] [int] IDENTITY (1, 1) NOT NULL,
    [my_data] [varchar] (50) NULL, 
    [created_on] [datetime] NOT NULL,
    [modified_on] [datetime] NOT NULL,
) ON [PRIMARY]

ALTER TABLE [my_table] WITH NOCHECK ADD 
    CONSTRAINT [PK_my_table] PRIMARY KEY  CLUSTERED ( [Id] )  ON [PRIMARY] 
GO 

ALTER TABLE [my_table] ADD 
    CONSTRAINT [DF_my_table_created_on] DEFAULT (getdate()) FOR [created_on]
GO 

ALTER TABLE [my_table] ADD 
    CONSTRAINT [DF_my_table_modified_on] DEFAULT (getdate()) FOR [modified_on]
GO

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    UPDATE my_table
    SET modified_on=getdate(), created_on=d.created_on
    FROM my_table INNER JOIN deleted d
    on my_table.id = d.id
END
GO

INSERT my_table (my_data) VALUES ('Nadine')
INSERT my_table (my_data) VALUES ('Haddon')
INSERT my_table (my_data) VALUES ('Alayna')
INSERT my_table (my_data) VALUES ('Kaleigh')
INSERT my_table (my_data) VALUES ('Evan')
INSERT my_table (my_data) VALUES ('Payton')

SELECT * FROM my_table

UPDATE my_table SET my_data = 'Nadine Cooper' WHERE my_data = 'Nadine'
UPDATE my_table SET my_data = 'Haddon Smith' WHERE my_data = 'Haddon'
UPDATE my_table SET my_data = 'Alayna Thomas' WHERE my_data = 'Alayna'
UPDATE my_table SET my_data = 'Kaleigh Willliams' WHERE my_data = 'Kaleigh'
UPDATE my_table SET my_data = 'Evan Roberts' WHERE my_data = 'Evan'
UPDATE my_table SET my_data = 'Payton Jones' WHERE my_data = 'Payton'

SELECT * FROM my_table

DROP my_table

quote:

---

Originally posted by Lamprey

This probably doesn't address your actual question, but why can't the import process handle updating these columns? Presumably, you (your company) is in control of this process and should be able to add that to the existing processing that is currently in place and avoid having to use triggers.

---

quote:

---

Originally posted by visakh16

are you telling that your staging table data gets modified by external processes other than etl ones?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---