-2

I get error "Procedure tr_employee_tbl_forinsert Column name or number of supplied values does not match table definition" when I run the INSERT query below. What should I do?

create table employee_tbl
(
EmpID int primary key,
FirstName varchar(50),
LastName varchar(50),
Salary float,
MobileNo varchar(15)
)

create table employeeaudit_tbl
(
EmpID int primary key,
AuditData varchar(50)
)
GO
create trigger tr_employee_tbl_forinsert
on employee_tbl
for insert
as
begin
        declare @EmpID int
        select @EmpID = EmpID from inserted

        insert into employeeaudit_tbl
        values('New employee with EmpID = ' + CAST(@EmpID as nvarchar(5)) +'is added at ' + CAST(GETDATE() as nvarchar(50)))
end
GO

marked as duplicate by Mikael Eriksson, Philᵀᴹ, Erik Darling, Martin Smith sql-server May 11 at 18:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Besides that error you should take attention on type and size of the column AuditData and the ones of the value you are trying to insert there. – Denis Rubashkin May 11 at 14:07
  • It’s a duplicate, but it’s also easy to answer that you have to specify the columns if you choose to use at least one less column then the table possesses. Oh, and if it’s hard to read (it is), formatting can save you! Put each column in the VALUES statement on its own line. The answer becomes obvious. – clifton_h May 11 at 16:01
0

Your missing a column just as the error says. Your INSERT Statement does not have the columns specified, so SQL Server assumes you are using all the columns in the order they are structured.

  • Always Specify columns in INSERTS
  • formatting can save you. Separate confusing parts of your code on new lines

And remember, Formatting will save you in the future if you invest in it.

create table employeeaudit_tbl
(
EmpID int primary key,
AuditData varchar(50)
)
GO
create trigger tr_employee_tbl_forinsert
on employee_tbl
for insert
as
begin
        declare @EmpID int
        select @EmpID = EmpID from inserted

/* Always specify your columns on inserts. It prevents this exact issue */
        insert into employeeaudit_tbl ( EmpID, AuditData )
        values(@EmpID -- ID
              ,'New employee with EmpID = ' + CAST(@EmpID as nvarchar(5)) +'is added at ' + CAST(GETDATE() as nvarchar(50)) --error
  )
end
GO

Design Consideration

But really, a better answer is why are you not using two extra columns in your Dimension Table for the employees?

  • Just add a DateCreated and a DateModified column and now you can track when they are created in the same table
  • use your auditing to track other important things.

Or, if your fancy and in MSSQL 2016+, use a temporal table. See the doc here: system-versioned temporal table - Microsoft Docs

0

You have several flaws in you design / code:

  • you should always specify the schema (default dbo) before the table names
  • you did not listed the columns in your insert statement (-> reason for the error)
  • your trigger will fail, when you insert more than one employee at one time
  • the audit table itself uses a bad design:
  • you should always place the timestamp and the action in a separate column (to be able to query the table again) and not string it together in a comment style column
  • you should not use the employee id as primary key, since you may want add further audit records, e.g. after resetting the password, changing permissions, disabling / reenabeling the employee.... This would fail with the EmpId as PK
  • you should consider to log, who has created the user (if everyone logs in to the SQL Server using his onw account and you are not using a single application login)

  • in your original trigger: it makes no sense to cast the GetDate() as NVARCHAR, when your destination column is only a VARCHAR

If it would my database I would try to start with something like this:

CREATE TABLE dbo.employeeaudit_tbl
(
    employeeaudit_id INT NOT NULL IDENTITY PRIMARY KEY,
    EmpID      INT,
    AuditTime DATETIME2(2),
    Action    VARCHAR(10),
    Comment   VARCHAR(50)
)
GO
CREATE TRIGGER tr_employee_tbl_forinsert
ON dbo.employee_tbl
FOR INSERT
AS
BEGIN
    INSERT INTO employeeaudit_tbl ( EmpID, AuditTime, Action, Comment )
    SELECT i.EmpId,
           GETDATE() AS AuditTime,
           'INSERT'  AS Action,
           NULL      AS Comment
      FROM inserted  AS i
    ;
END
GO

Not the answer you're looking for? Browse other questions tagged or ask your own question.