question

AlexKlap avatar image
AlexKlap asked

Trigger Issue

Hello All, I have a table with few columns in it. i have created a trigger (AFTER UPDATE) to trace changes occured to 1 column. whenever any change occur i am storing data to another table pulling changed record from magic table recently i am facing an issue, whenever user want to update records through Application, they won't be able to do so. but as soon as we disable the trigger they are able to update records. Can someone please guide me what might be issue. /* Trigger code CREATE TRIGGER [dbo].[long_Description_History] ON [dbo].[table1] AFTER UPDATE AS SET NOCOUNT ON; IF ( UPDATE (long_description)) BEGIN INSERT_Description_History ( ROLLNO ,CLASS ,long_description ,SUBJECTS , Modified_Date ) SELECT ROLLNO,CLASS,long_description,SUBJECTS,getdate() FROM deleted END */ Thanks
trigger
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
What error do you get when the application tries to update records? Does the application connection have permission to insert into talkpic.dbo.Part_Description_History?
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Kev: Application is connected with table 1 where its inserting data. talkpic.dbo.Part_Description_History is populated from deleted. which further used in SSRS. concern is application allow to modify some records and sometimes its doesn't. but if i disable the trigger then issue got resolved. and user can modify column without any problem. i am not sure whether update trigger caused any type of blocking. Thanks.
0 Likes 0 ·
@Kev: Hi Kev, error message is An undetermined error occured during the update Please help me.
0 Likes 0 ·
Nothing in the code you've given would suggest blocking. Is that the entire error message - is there no error number?
0 Likes 0 ·
@Kev: Hi Kev Greetings. when i did some testing, it looks like trigger not fired at all. i updated the column and checked in table Part_Description_History for the updated record, and its not logged there. but after truncation table Part_Description_History, and re-run the update its getting logged for only first time. for eg:- if first time we changed long_description from dog to 'cat', trigger fired and dog got inserted in history table next time again when i am changing Cat to lion but in history table same 'Dog' record got appended twice instead of cat. Please suggest. Thanks a lot... :)
0 Likes 0 ·
so trigger is firing but adding the same data?
0 Likes 0 ·
Show more comments
jimsnyder avatar image
jimsnyder answered

@AlexKlap You have INSERT_Description_History, but not target table for it to go into. Are you missing a bit of query pasted here, or is that an error?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.