Create trigger to know who made changes with date SQL Server Table

[gelezniyden]

Hello guys,

I have one table Products and a second Products_audit with two columns User and Date. I need to have trigger which performs following actions:
When anybody will modify data in Products, I need trigger to insert the User's name and Date of inserting into the Products_audit in appropriate columns. I wrote following:

CREATE TRIGGER Super1 ON  Products
   AFTER UPDATE, INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Products_audit (Date, [User])
    SELECT suser_sname(),getdate() FROM Products
 
END 

When I try to insert data into Products I get error:

Server: Msg 515, Level 16, State 2, Procedure Super1, Line 6
Cannot insert the value NULL into column 'Discontinued', table 'Teachdb.dbo.Products_audit'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I don't understand why it points out on 'Discontinued' column ? Note: before trigger was created I didn't face with that issue when I inserted data.

[Rabbit]

It's because if you don't define a value to insert into the field, by default it uses NULL. And your table definition doesn't allow NULLs. If you never got that message when you inserted stuff before, that means you defined a value to insert.