Insert Trigger for Non null columns in SQL Server

问题:

I have a followed table:

JobSkill

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

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

I need to create trigger for inserting CreatedDate and ModifiedDate.

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.

回答1:

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.

There is a two solution:

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

2. 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.