CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Wachburn
Wachburn

Reputation: 2939

Trigger after insert on not null column

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?

Upvotes: 13

Views: 13311

Answers (4)

Brian Wenhold
Brian Wenhold

Reputation: 349

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.

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 2

fancyPants
fancyPants

Reputation: 51888

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

Upvotes: 3

TechDo
TechDo

Reputation: 18629

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

Upvotes: 2

Related Questions