CODEWITHSUNDEEP
sql-server
Asha Pradhan
Asha Pradhan

Reputation: 1

while creating a trigger I got an error ie incorrect syntax near end

ALTER TRIGGER [dbo].[student_on_insert]
ON [dbo].[Student]
FOR INSERT 
AS
BEGIN
    SELECT * FROM student

    DECLARE @id INT

    SELECT @id = rollno FROM inserted

    INSERT INTO [dbo].student_adm
    VALUES (@id, 'student having rollno' + CAST(@id AS VARCHAR(4)) + ' was admitted .' + CAST(GETDATE() AS VARCHAR(50))
END

Upvotes: 0

Views: 34

Answers (2)

marc_s
marc_s

Reputation: 754518

Your trigger might get multiple rows in the Inserted pseudo table - you can therefore NOT just do a selection like this:

SELECT @id = rollno FROM inserted

This would select one arbitrary row out of all the rows being inserted - and ignore all the others.

Instead, you need to use a set-based approach and deal with the fact that multiple rows might have been inserted. You need to change your code to something like this:

ALTER TRIGGER [dbo].[student_on_insert]
ON [dbo].[Student]
FOR INSERT 
AS
BEGIN
    INSERT INTO [dbo].student_adm
        SELECT 
            i.rollno,  
            'Student with rollno: ' + CAST(@id AS VARCHAR(4)) + 
                 ' was admitted. ' + CAST(GETDATE() AS VARCHAR(50))
        FROM 
            Inserted i
END

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

One missing parenthesisat end of values(..) statement. Try below

ALTER trigger [dbo].[student_on_insert]
on [dbo].[Student]
for insert 
as
begin
        select * from student
        declare @id int
        select @id=rollno from inserted
        insert into [dbo].student_adm
        values(@id,'student having rollno'+cast(@id as varchar(4))+' was admitted .'+cast(getdate() as varchar(50)))
end

Upvotes: 0

Related Questions