在非空列上插入后触发

时间:2022-09-28 22:56:26

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.

不能将值NULL插入到'Created'、表'Category';列不允许null。插入失败。

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?

如何在不设置允许null的情况下编写此类触发器?

4 个解决方案

#1


3  

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.

将“创建到”列的默认值设置为0。遗憾的是,MySQL不允许在一个表中使用默认的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

#2


2  

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

因此,为了消除错误,您可以插入/填充创建的列,并与insert一起更新。或者您可以添加列的默认值属性。详情请查看链接

Add column, with default value, to existing table in SQL Server

在SQL Server中添加默认值的列到现有表。

Specify Default Values for Columns

为列指定默认值

#3


1  

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

#4


0  

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:

我通常允许最后更新的列为null,因为我想知道它是否从未被修改过。如果你必须让这两个字段都为空,我会在这些列中添加一个默认值:

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.

如果您真的想要使用触发器,那么您将不得不指定而不是在后面加上insert语句。

#1


3  

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.

将“创建到”列的默认值设置为0。遗憾的是,MySQL不允许在一个表中使用默认的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

#2


2  

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

因此,为了消除错误,您可以插入/填充创建的列,并与insert一起更新。或者您可以添加列的默认值属性。详情请查看链接

Add column, with default value, to existing table in SQL Server

在SQL Server中添加默认值的列到现有表。

Specify Default Values for Columns

为列指定默认值

#3


1  

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

#4


0  

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:

我通常允许最后更新的列为null,因为我想知道它是否从未被修改过。如果你必须让这两个字段都为空,我会在这些列中添加一个默认值:

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.

如果您真的想要使用触发器,那么您将不得不指定而不是在后面加上insert语句。