在SQL Server中为非空列插入触发器 [英] Insert Trigger for Non null columns in SQL Server

问题描述

我有一张跟踪表：

工作技能

CreatedByID not null, 
CreatedDate not null, 
ModifiedByID not null, 
ModifiedDate not null

我需要按过程和触发器插入一条记录。通过过程插入
CreatedByID 和 ModifiedByID 。

I need to insert a record by procedure and trigger. CreatedByID and ModifiedByID are inserted through procedure.

我需要创建插入 CreatedDate 和 ModifiedDate 的触发器。

I need to create trigger for inserting CreatedDate and ModifiedDate.

在MSSQL中执行插入方法时，出现此错误。

When I am executing insert method in MSSQL, I am getting this error.

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CreatedDate', table 'One91.dbo.JobSkill'; column does not allow nulls. INSERT fails.
The statement has been terminated.

有人可以帮我解决这个问题吗？

Can anyone help me in this issue pls.

推荐答案

问题在于，SQL一步尝试尝试填充所有4列，但对于 CreatedDate ，您只提供了2个值。和 ModifiedDate 。因为所有列都不为空，所以SQL不能使列的值丢失。

Problem is with that in one step SQL try to fill all 4 columns, but you provide only 2 values, for CreatedDate and ModifiedDate. Because all columns have NOT NULL, SQL cannot leave columns with missing values.

有两种解决方案：

1. 将列 CreatedDate 和 ModifiedDate 更改为NULL，因此在过程中它们将为NULL，但使用触发器将填充它。

1. Change columns CreatedDate and ModifiedDate as NULL, so in procedure they will be NULL, but with trigger you will fill it.

CreatedDate 和 ModifiedDate 可以保留为NOT NULL列，但是您必须创建 DEFAULT 约束，以确保在过程中使用某些默认值填充列（或使用某些任意值手动填充列）在过程中），然后使用触发器对其进行更新。

CreatedDate and ModifiedDate can stay as NOT NULL columns, but you must create DEFAULT constraint which will assure that columns to be filled in procedure with some default values (or fill manually with some arbitrary values in procedure), and then update them with trigger.

这篇关于在SQL Server中为非空列插入触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！