Trigger after insert on not null column

Question

I have a table that contains two not null columns Created and Updated.

I wrote corresponding triggers

ALTER TRIGGER [dbo].[tr_category_inserted] ON [dbo].[Category]
AFTER INSERT
AS
BEGIN
  UPDATE Category
  SET Created = GETDATE(), Updated = GETDATE()
  FROM inserted
  WHERE Category.ID = inserted.ID;
END

and

ALTER TRIGGER [dbo].[tr_category_updated] ON [dbo].[Category]
AFTER UPDATE
AS
BEGIN
  UPDATE Category
  SET Updated = GETDATE()
    FROM inserted
        inner join [dbo].[Category] c on c.ID = inserted.ID
END

but if I am inserting a new row I get an error

Cannot insert the value NULL into column 'Created', table 'Category'; column does not allow nulls. INSERT fails.

Insert command:

INSERT INTO [Category]([Name], [ShowInMenu], [Deleted])
     VALUES ('category1', 0, 0)

How can I write such triggers without a setting to these columns to allow null?

If you want to update date automatically on insert, i suggest you to use default constraint instead of triggers — Hary
Error occurring because trigger works after insertion only, and you may not be inserting the column values Created and Updated at the time of insert. — TechDo
As the name of the trigger already says, the trigger fires AFTER the insert has happened - but you need to provide a value upon INSERT for any NOT NULL column. So you either need to write an INSTEAD OF INSERT trigger to set the initial value of those columns -or much easier: just define a DEFAULT (GETDATE()) constraint on that column so it will automatically be filled on INSERT .... — marc_s

fancyPants fancyPants · Accepted Answer · 2013-01-24T09:27:14

Modify your table like this:

ALTER TABLE yourTable MODIFY COLUMN updated timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE yourTable MODIFY COLUMN created timestamp DEFAULT 0;

Set the default for the created to column to 0. Unfortunately MySQL does not allow two timestamp columns with default CURRENT_TIMESTAMP in one table. To overcome this you just have to insert a NULL value into created column and you will have both columns to the current timestamp.

INSERT INTO yourTable (col1, created) VALUES ('whatever', NULL);

Or you set the default to a valid timestamp like

ALTER TABLE yourTable MODIFY COLUMN created timestamp DEFAULT '1970-01-01 00:00:00';

and modify your trigger like this:

ALTER TRIGGER [dbo].[tr_category_inserted] ON [dbo].[Category]
AFTER INSERT
AS
BEGIN
  UPDATE Category
  SET Created = GETDATE()
/*  FROM inserted */ /*don't know where you got that from, do you port from SQL Server?*/
  WHERE Category.ID = NEW.ID;
END

Trigger after insert on not null column

4 Answers