Home > DeveloperSection > Forums > Trigger after insert on not null column
ezra heywood
ezra heywood

Total Post:145

Points:1019
Posted on    May-04-2013 1:58 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 829  View(s)
Rate this:
Hi Expert!

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?

Thanks in advance!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-04-2013 8:50 AM

Hi Ezra!

You can try as following

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

I hope it's helpful to you!

Don't want to miss updates? Please click the below button!

Follow MindStick