sql - 在非空列上插入后触发

Question

我有一个表，其中包含两个 not null 列 Created 和 Updated。

我写了相应的触发器

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

和

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

但是如果我插入新行，则会收到错误

Cannot insert the value NULL into column 'Created', table 'Category'; column does not allow nulls. INSERT fails.

插入命令:

INSERT INTO [Category]([Name], [ShowInMenu], [Deleted])
     VALUES ('category1', 0, 0)

如何编写此类触发器而不将这些列设置为允许为空？

Answer 1

像这样修改你的表:

ALTER TABLE yourTable MODIFY COLUMN updated timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE yourTable MODIFY COLUMN created timestamp DEFAULT 0;

将创建的列的默认值设置为 0。不幸的是，MySQL 不允许在一张表中存在两个具有默认 CURRENT_TIMESTAMP 的时间戳列。要克服这个问题，您只需将 NULL 值插入到 created 列中，两列都将具有当前时间戳。

INSERT INTO yourTable (col1, created) VALUES ('whatever', NULL);

或者您将默认值设置为有效时间戳，例如

ALTER TABLE yourTable MODIFY COLUMN created timestamp DEFAULT '1970-01-01 00:00:00';

并像这样修改你的触发器:

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