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, Jan 24 '13 at 08:54
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, Jan 24 '13 at 09:21
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, Jan 24 '13 at 09:29

score 3 · Accepted Answer · answered Jan 24 '13 at 09:27

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

score 2 · Answer 2 · edited May 23 '17 at 10:29

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.

So for eliminating the error, you can insert/populate the columns Created and Updated along with insert. OR You can add default value property of column. Please check the links for details

Add column, with default value, to existing table in SQL Server

Specify Default Values for Columns

score 2 · Answer 3 · answered Jan 24 '13 at 09:54

Possible use INSTEAD OF trigger

CREATE TRIGGER [dbo].[tr_category_inserted] ON [dbo].[Category]
INSTEAD OF INSERT
AS
BEGIN
  INSERT Category(Name, ShowInMenu, Deleted, Created, Updated)
  SELECT Name, ShowInMenu, Deleted, GETDATE(), GETDATE()
  FROM inserted i
END

score 0 · Answer 4 · edited Dec 18 '13 at 16:48

I typically allow the last updated column to be null since I want to know if its never been altered. If you must keep both fields nullable I would add a default value to those columns like this:

ALTER TABLE [dbo].[Category] ADD  DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[Category] ADD  DEFAULT (getdate()) FOR [LastUpdated]
GO

Then you won't need the trigger for the insert.

If you really want to use a trigger anyway you will have to specify INSTEAD OF rather than AFTER and include the insert statement.

Trigger after insert on not null column

4 Answers4