How can i insert data into a log table after update using triggers?

Question

I am new to triggers and i am trying to insert data into a log table after the Alumns table was updated.

| Alumn_ID (PK) | Name   |  Courses    | Favourite_Course
      1             Peter       5                3

| Log_ID | Alumn_ID |   Note  
     1          1       Number of courses was changed

For some reason it is not letting me insert the Alumn ID and the trigger doesn't work, also i can't find how to insert the log id as identity

ALTER TRIGGER [LOG]
ON ALUMNS
AFTER UPDATE
AS
BEGIN

DECLARE @Note VARCHAR(50)

DECLARE @Alumn_ID varchar;
SELECT @Alumn_ID= INSERTED.Alumn_ID FROM INSERTED

IF UPDATE(Name)
BEGIN
SET @Note = 'Updated Name'
END


INSERT INTO Alumn_Log (Log_ID, Note)
VALUES (@Alumn_ID, '@Alumn')
END

After that i am trying to modify the name to see if the trigger works and i get the next error:

ERROR:

No row was updated

The data in row 1 was not commited. Error Source: .Net SqlClient Data Provider. Error Message: instruction INSERT is in conflict with restriction FOREIGN KEY "FK_ALUMN_LOG_ALUMN_ID". The conflict has appeared in database "SCHOOL", table "dbo.ALUMNS", Column "ALUMN_ID." Instruction ended.

Correct the errors and retry or press ESC to cancel the change(s).

Firstly, you've made the usual beginners mistake of thinking that INSERTED has only one record. Everyone does it. What does "not letting me insert the Alumn ID and the trigger doesn't work" mean. Can you describe, with code and error messages, what you are doing and in what way it doesn't work. Please edit your question as this is key to the question — Nick.Mc
– Nick.Mc, Commented Apr 23, 2018 at 5:46
is says something about a problem with foreing key, it does nothing, doesn't let me update any row, is the trigger well written? — Dog
– Dog, Commented Apr 23, 2018 at 5:50
Please edit your question and actually post the error. No the trigger is not well written. It will crash if you insert or update more than one row — Nick.Mc
– Nick.Mc, Commented Apr 23, 2018 at 5:51
add your error and table structure in the question, if possible — Rahul Jain
– Rahul Jain, Commented Apr 23, 2018 at 5:53
I see that out of eight questions with good answers, you have not marked any as answered. Please consider posting reasonable questions and marking answers correctly. — Nick.Mc
– Nick.Mc, Commented Apr 23, 2018 at 5:58

Gordon Linoff · Accepted Answer · 2018-04-23 11:47:04Z

3

Presumably, the trigger doesn't work because id has more than one character.

I don't know what @Note is supposed to be doing, because it is not being used. So, your logic can be reduced to:

ALTER TRIGGER [LOG] ON ALUMNS AFTER UPDATE AS
BEGIN
    INSERT INTO Alumn_Log (Log_ID, Note)
        SELECT i.Alumn_ID, '@Alumn'
        FROM inserted i;
END;

answered Apr 23, 2018 at 11:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can i insert data into a log table after update using triggers?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related