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语句。