DateCreated column on Sql server?

Question

Is there a special way to declare a DateCreated column in an MS Sql Server table so that it automatically populates it with the appropriate timestamp when it is created?

Or .. Do I have to provide the date and time when I execute the request manually?

Answer 1

Default values ​​have two main drawbacks.

• If the insert statement specifies a value for a column, the default value is not used.
• The column can be updated at any time.

This means that you cannot be sure that the values ​​have not been changed outside your control.

( , - ), .

(, , ) - DateCreated.

- DateModified.

( Gabriel - , - 100%, , OP ...):

CREATE TRIGGER [dbo].[tr_Affiliate_IU] 
   ON  [dbo].[Affiliate] 
   AFTER INSERT, UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the current date.
    DECLARE @getDate DATETIME = GETDATE()

    -- Set the initial values of date_created and date_modified.
    UPDATE
        dbo.Affiliate
    SET 
         date_created = @getDate
    FROM
        dbo.Affiliate A 
        INNER JOIN INSERTED I ON A.id = I.id
        LEFT OUTER JOIN DELETED D ON I.id = D.id
    WHERE
        D.id IS NULL

    -- Ensure the value of date_created does never changes.
    -- Update the value of date_modified to the current date.
    UPDATE
        dbo.Affiliate
    SET
         date_created = D.date_created
        ,date_modified = @getDate
    FROM 
        dbo.Affiliate A 
        INNER JOIN INSERTED I ON A.id = I.id
        INNER JOIN DELETED D ON I.id = D.id 
END

Answer 2

"getdate()"

Answer 3

CreatedDate

, - . .

, Application Bug CreateDate ( DBA DBA)

, CreateDate.

, INSERT TRIGGER 1:1, . SELECT, UPDATE , , UPDATE CreateDate - "" .

, UPDATE/DELETE , ( , "" )

It’s a little pain to have an extra table, though ... may have one table for all CreateDates - TableName, PK, CreateDate. Most database architects will hate this, though ...

Answer 4

Of course have.

Here is an example action for you.

Create table #TableName
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    SomeDate VARCHAR(100)
)

INSERT INTO #TableName (SomeDate)
SELECT 'Some data one' UNION ALL SELECT 'some data two' 

SELECT * FROM #TableName 

DROP TABLE #TableName