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

select * from employee_tbl
select * from employeeaudit_tbl

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

closed as unclear what you're asking by Colin 't Hart, Erik Darling, kevinsky, John Eisbrener, mustaccio May 13 at 14:15

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

Your error message:

Column name or number of supplied values does not match table definition.

Your trigger code:

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

If you read the error message clearly it is telling you that one of the things that you are attempting to do has an incorrect number of arguments when compared to the table definition.

Looking at your table definition and insert, we see:

  • Table: 2 columns
  • Insert statement: 1 value provided

Therefore your INSERT statement needs changing. You haven't provided the EmpID.

insert into employeeaudit_tbl
        values(@EmpID, 'New employee with EmpID = ' + CAST(@EmpID as nvarchar(5)) +'is added at ' + CAST(GETDATE() as nvarchar(50)))
  • Agree with the diagnosis entirely, but EmpID would be wrong to use in values like that, because it doesn't look like either a variable name or a function name, and other kinds of names would be invalid in that context. Replacing it with @EmpID would seem to me incorrect too, because the audit table is likely supposed to have its own set of IDs independent of the employee table. I mean, the OP is clearly confused but the proposed solution example might confuse them more. As we don't know how the ID is supposed to be generated, maybe it should be some kind of a placeholder there. – Andriy M May 11 at 15:12
  • An alternative solution might be to suggest that the audit table's ID column be redefined as an identity column. – Andriy M May 11 at 15:14
  • 1
    @AndriyM I thought the whole audit thing was initially wrong too, but it's only auditing ON INSERT, so there will be a 1-to-1 relationship between the two tables – Philᵀᴹ May 11 at 15:20
  • That's a good point, didn't realise that. – Andriy M May 11 at 15:30
  • 1
    The logic here is still going to fail to properly audit a multi-row insert (e.g. insert dbo.employee_tbl(empID, etc.) VALUES(1, etc.),(2, etc.);). – Aaron Bertrand May 11 at 19:44

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