DML trigger error for insert: Column name or number of supplied values does not match table definition

Question

I have tables Employee and EmployeeUpdate defined like so:

CREATE TABLE [dbo].[tblemployee](
    [id] [int] NULL,
    [name] [nchar](10) NULL,
    [salary] [int] NULL,
    [gender] [nchar](10) NULL,
    [departmentid] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tbl_employee_forinsert](
    [id] [int] NULL,
    [auditdata] [nchar](10) NULL
) ON [PRIMARY]

Data:

select * from employee

id  name    salary  gender  departmentid
1   jhon        5000    male        3
2   mike        3400    male        2
3   pam         6000    female      1
4   todd        4800    male        4
5   sara        3200    female      1
6   ben         4800    male        3
101 ram         50050   male        33
102 ram         50050   male        33

I want to use DML triggers for inserting the row in EmployeeUpdate, for which I use this code:

create trigger tr_tbl_employee_forinsert
  on tblemployee
  for insert
  as
  begin

        declare @id int
        select @id = id from inserted
        insert into  [tbl_employee_forinsert]
        values ('New employee with id = ' + cast (@id as nvarchar(5)) + '  is added at   '+ cast ( Getdate () as nvarchar(20)))
    end

I am facing this error:

Msg 213, Level 16, State 1, Procedure tr_tbl_employee_forinsert, Line 9 Column name or number of supplied values does not match table definition.

In addition to this error you also have a big problem with your logic since you're assuming there only one row in inserted, which isn't always true — James Z, Jul 16 '15 at 12:41
To those who vote to close: realize that the duplicates you flagged only solves the most apparent problem with the question. Even if the OP applied the solutions from the flagged questions, other issues would remain; both answers here address those further issues. — jpw, Jul 16 '15 at 19:50

score 0 · Answer 1 · answered Jul 16 '15 at 12:45

This should work:

create trigger tr_tbl_employee_forinsert
on tblemployee for insert as
begin

    insert into  [tbl_employee_forinsert] (id, auditdata)
    select id, 'New employee with id = ' + cast (id as nvarchar(5)) + '  is added at   '+ cast (Getdate () as nvarchar(20))
    from inserted
end

Although your table can only store nchar(10) into auditdata so you'll have to fix that too

score 0 · Answer 2 · answered Jul 16 '15 at 12:49

Since you have defined the table with two columns:

CREATE TABLE [dbo].[tbl_employee_forinsert](
    [id] [int] NULL,
    [auditdata] [nchar](10) NULL
) ON [PRIMARY]

you have to supply values for both columns or specify what column to insert into in the insert statement.

However, there's two more serious issues with the code:

1.You can't do select @id = id from inserted as the inserted virtual table holds a set of data, and not a single item.

2.You're trying to insert way more characters than your column (declared as nchar(10)) have room for, so you need to expand the column.

Assuming you want the id of the employee inserted in the id column, the trigger you want is probably something like this:

create trigger tr_tbl_employee_forinsert
on tblemployee for insert as
  begin
    insert into  [tbl_employee_forinsert] (id, auditdata)
    select i.id, 'New employee with id = ' + cast (i.id as nvarchar(5)) + ' is added at ' + cast (Getdate () as nvarchar(20)))
    from inserted i
  end

In the query you mention a table EmployeeUpdate, but in the definition you have a table tbl_employee_forinsert which I guess is the one you refer to, so change name if it doesn't match

DML trigger error for insert: Column name or number of supplied values does not match table definition

2 Answers2