CODEWITHSUNDEEP
sql-serverdatetimetriggersinsert
user127815
user127815

Reputation: 141

Implicit conversion from data type datetime to numeric is not allowed

Getting the following error: Line 21 is the declare statement. I can't figure this out. Done_ON is clearly of type datetime, why is the compiler complaining? Thanks.

Msg 257, Level 16, State 3, Procedure insert_employee_details, Line 21 Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.

Use [StacysDB];
Go


--CREATE TABLE DB_Actions
--(
--Id numeric(5,0) IDENTITY(1,1) PRIMARY KEY,
--Table_Name varchar(20),
--Action_Name varchar(10),
--User_Name varchar(50),
--Done_ON datetime,
--Record_Id numeric(5,0)
--);


--INSERT TRIGGER
CREATE TRIGGER insert_employee_details
ON Employee_Details
FOR INSERT
AS
DECLARE @id int, @name varchar(20)
SELECT @id = Emp_Id, @name = Emp_First_Name FROM inserted
INSERT INTO DB_Actions
VALUES(@id,
        'Employee_Details',
        'INSERT',
        @name,
        getdate()
)

Upvotes: 2

Views: 10143

Answers (2)

Z Wizard
Z Wizard

Reputation: 11

Id column in table DB_Actions is identity column, not allowed for INSERT statement in the trigger, so you miss fifth column, thus SQL Server tried to convert GetDate() into Record_Id, hence caused the error.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You should always list the columns for insert:

INSERT INTO DB_Actions(id, table_name, action_name, user_name, done_on)
    VALUES(@id,
            'Employee_Details',
            'INSERT',
            @name,
            getdate()
    );

You are missing the Record_Id. I'm not sure why it would generate this error, though. You would get this error if Record_Id is really the 5th column in the table.

A more proper way to write the trigger is to assume that inserted could have multiple rows:

INSERT INTO DB_Actions(id, table_name, action_name, user_name, done_on)
    SELECT Emp_Id, 'Employee_Details', 'INSERT', Emp_First_Name, getdate()
    FROM inserted;

This, at least, will not generate an error sometime when you insert multiple rows at the same time.

Upvotes: 3

Related Questions