CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nitin Aggarwal
Nitin Aggarwal

Reputation: 461

Prevent changes the default value of a column in a table sql server

How can i prevent changes in a column value set to default in sql server

i have a table TimeTable in which Column is date which is of type varchar(20) i want that column should be updated with getdate() all the time when user insert the value and prevent from modify/change the value in insert statement or update statement. please help!

Upvotes: 0

Views: 1661

Answers (3)

Devart
Devart

Reputation: 122042

Use view + trigger -

CREATE TABLE dbo.TestTable
(
      ID INT IDENTITY(1,1) PRIMARY KEY
    , ProductName VARCHAR(25)
    , PurchargeDate DATETIME DEFAULT(GETDATE())
)
GO

CREATE VIEW dbo.vw_TestTable
AS
    SELECT 
          ID
        , ProductName
        , PurchargeDate
    FROM dbo.TestTable

GO

CREATE TRIGGER dbo.trg_IOIU_TestTable

   ON dbo.vw_TestTable
   INSTEAD OF INSERT, UPDATE

AS BEGIN

    SET NOCOUNT ON;

    INSERT INTO dbo.TestTable(ProductName)
    SELECT i.ProductName
    FROM INSERTED i
    LEFT JOIN dbo.TestTable t ON t.ID = i.ID
    WHERE t.ID IS NULL

    UPDATE t
    SET ProductName = i.ProductName
    FROM dbo.TestTable t
    JOIN INSERTED i ON t.ID = i.ID

END
GO

Upvotes: 1

peterm
peterm

Reputation: 92845

Use trigger for that. It might look like this

CREATE TRIGGER tg_default_value ON TimeTable
FOR INSERT, UPDATE
AS
UPDATE t
   SET t.[Column] = GETDATE()
  FROM TimeTable t JOIN INSERTED i 
    ON t.ID = i.ID;

See SQLFiddle example

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

I think use of trigger will be good approach to do this. In this trigger if opration is insert then you can insert the gatdate() value after change the format to char by using to_char(). and if operation is update then you can prevent to change the value for this column.

Upvotes: 0

Related Questions