Jimmysnn Jimmysnn - 3 months ago 13
SQL Question

execute constraint on update sql

I have a constraint that return datetime for a column (date).
On insert work it! Now I would like update column date with new value of getdate() when an update statement execute. Any idea?

My constraint:

ALTER TABLE [tablename] ADD CONSTRAINT [constraintName]
DEFAULT (getdate()) FOR [column]


MY DBMS is Microsoft SQL Server 2012

Answer

You can do it using a trigger like this:

CREATE TRIGGER updTableDate
ON [Table] 
AFTER INSERT, UPDATE   
AS
BEGIN
SET NOCOUNT ON;
    UPDATE [Table] SET modified = GETDATE() WHERE [primary_key] IN (SELECT [primary_key] FROM INSERTED)
END

Or better:

CREATE TRIGGER updTableDate
ON [Table] 
AFTER UPDATE   
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [Table] t SET modified = GetDate() FROM INSERTED AS i WHERE t.id = i.id 
END
Comments