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?

---

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

---

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

---

可能使用INSTEAD OF触发器

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

链接地址: http://www.djcxy.com/p/24720.html

上一篇: 无法将计算列设置为空值时遇到问题

下一篇: 在非空列上插入后触发