2

Ok, so I'm trying to update a column to a CURRENT_TIMESTAMP in a specific record whenever the record is originally inserted. This is what I've got but otherwise I am lost.

CREATE TRIGGER dbo.TrgUpnInsert
  ON [CNLH Security Authentication].dbo.DateTimestamp
    AFTER INSERT, 
    INSERT INTO [CNLH Security Authentication].dbo.EntranceInformation (DateTimestamp) Values (CURRENT_TIMESTAMP);
  • @a_horse_with_no_name SQL Server – AArmentrout Nov 17 '16 at 17:49
  • If you're performing an update you would want to update rather than insert. And you would need to specify the row you want to update (so as to not update the entire table). Do you have associated columns between the two tables that would help you identify the relevant row in the EntranceInformation table? – Nic Nov 17 '16 at 18:17
6

Your trigger needs to leverage the 'virtual inserted' table as in the following example:

CREATE TRIGGER ModDate
    ON TableX
    AFTER INSERT
AS
BEGIN
    UPDATE X 
    SET ModifiedDate = CURRENT_TIMESTAMP
    FROM TableX X
    JOIN inserted i ON X.key = i.key -- change to whatever key identifies 
                                     -- the tuples
END 

AS an alternative to a trigger, why not simply assign a default value to that column. Example:

ALTER TABLE myTable 
  ADD CONSTRAINT CONSTRAINT_NAME
    DEFAULT CURRENT_TIMESTAMP FOR myColumn

example:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

CREATE TABLE #temp (
    col1 VARCHAR(10)
    ,col2 DATETIME
    )

ALTER TABLE #temp ADD CONSTRAINT ConstraintForTemp DEFAULT CURRENT_TIMESTAMP
FOR col2

INSERT INTO #temp (col1)
VALUES ('abc')

SELECT *
FROM #temp
  • If you only care about the datetime when the record was created, definitely go with assigning default value to the column. Triggers are powerful but are easily overlooked when making further changes to the system. – datagod Nov 17 '16 at 18:30
  • So I tried to use the constraint, but the column is coming up as null and I get an error that says "This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this. the displayed data in this row is read only. To fix this problem, please rerun the query." But, when I try to rerun the query, it comes back with an error stating that the column already has a default value. – AArmentrout Nov 17 '16 at 19:17
  • See my updated answer for an example. Is there a difference between that example and what you are trying to do? – Scott Hodgin Nov 17 '16 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.