TRIGGER AFTER INSERT ON NOT NULL COLUMN

ezra heywood

Total Post:145

Points:1019
Posted by  ezra heywood
 1085  View(s)
Ratings:
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!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: Trigger after insert on not null column

    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!

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!