HomeОбразованиеRelated VideosMore From: kudvenkat

After update trigger Part 44

598 ratings | 189431 views
In this video we will learn about After Update DML trigger. We will also write simple trigger which audits employee updates. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/after-update-trigger-part-44.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-44-dml-triggers.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Html code for embedding videos on your blog
Text Comments (90)
Again and again I am telling..u r a born teacher...Hats off u for ur effort sir..finally understood..thank u so much.. gd bless u..
Awais Butt (1 year ago)
Its more then useful. Its an asset
kudvenkat (2 years ago)
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat
Why do you created a copy of inserted table into a temporary table while deleted table was directly used. Ok..Got it....It was used to exit while loop else it would have looped infinitely.
J P (2 months ago)
Will this work if you are updating multiple records at a time? Say you have 50 columns, and I need to apply a last_modified date to my final table field; should any one column be updated with new value. Is that possible with current SQL presented, using Select Top 1 inside your trigger?
J P (2 months ago)
2:50 My question is on this part, "Select * from deleted & Select * from inserted". Are those tables you created before the video OR are those table names a default choice for the trigger update (you mentioned them as special tables). What I'm trying to understand and I can't at the moment, is, how do those two tables work/interact with the rest of the SQL script?...thank you in advance. After testing myself it makes sense now, I just had to try it for the first time. Looks like I am also a hands on learner. Thank you so much for the time you take to do this for us. God Bless you and your family.
Shahbaz Alam (2 months ago)
Wow...I am a fan
Zamzoki (3 months ago)
Why did salary increase when you changed gender from female to male at 3:30?
Xavier Dupont (4 months ago)
I think that this trigger example is among the most horrendous SQL code that may exist on this plane. Very good for illustrating how triggers work. But as bad as it could be when it comes to putting that stuff in production.
Mouad Nassri (5 months ago)
Thanks a lot sir, my problem is that i understand the logic and all statements and how it work but for exemple if i don't watch this video i will forgot some logic statement ! anyone can give me some advices to master the logic in my brain
pramod maurya (6 months ago)
Thanks for your knowledge sharing..
Mohammed Alkhafaf (7 months ago)
it is very useful thanks
king naveen (7 months ago)
hello sir,in this video,your mention slide-42(DML Trriger),but actualy slide-42 is (View limitations concept).
Xiaodong Zhang (9 months ago)
I think using varibles is too complicated. You should use insert into ''''''' select ''''' from inserted/deleted
mohan prabhu (10 months ago)
can we make as a previous coloum nd present coloum ....and what is the query for that my HR is asking me to do like that
MD ARIF (10 months ago)
loop taking much time to be executed mean i couldn't get see result its not being executed
epuri raghavendra (11 months ago)
I think no one else can explain as simple as you , you are an exceptional one sir, thanks a lot :)
Reynaldo Duazo Jr (11 months ago)
This is very helpful, i thank you very much for your effort sir, thumbs up (y) !
Sem Rithiro (1 year ago)
Where will deleted row go when we delete it from deleted table?
Hridaya Annuncio (1 year ago)
Superb videos! I have finally been able to understand triggers! thank you so much
poor Todd. why such decrease in salary?
Awais Butt (1 year ago)
Hello Sir, hope you are fine! Can we get the slides as well??
hamza nazir (1 year ago)
awesome sir amazing
SAJID HUSSAIN (1 year ago)
sir, After successfully creating update trigger when i am updating a row in table the following error showing as output.. Error: (1 row(s) affected) Msg 8152, Level 16, State 13, Procedure TR_Audit_employeeUpdate, Line 52 String or binary data would be truncated. The statement has been terminated. Update Trigger code is: Alter trigger TR_Audit_employeeUpdate on employee for update as Begin declare @Id int declare @oldname nvarchar(20), @newname nvarchar(20) declare @oldsalary int, @newsalary int declare @oldGender nvarchar(20), @newGender nvarchar(20) declare @oldCity nvarchar(20), @newCity nvarchar(20) declare @oldDeptId int, @newDeptId int declare @AuditString nvarchar(1000) select * into #TempTable from inserted while(Exists(select Id from #TempTable)) Begin set @AuditString = '' select top 1 @Id = ID , @newname = Name, @newGender = Gender, @newsalary = Salary, @newCity = City,@newDeptId = DepartmentID from #TempTable select @oldname = Name, @oldGender = Gender, @oldsalary = Salary, @oldCity = City, @oldDeptId = DepartmentID from deleted where ID = @Id set @AuditString = 'Employee with ID = '+ CAST(@Id as nvarchar(4))+ 'changed ' if(@oldname <> @newname) set @AuditString = @AuditString + ' Name from ' [email protected]+ ' to ' + @newname if(@oldGender<>@newGender) set @AuditString = @AuditString + ' Gender from ' + @oldGender + ' to ' + @newGender if(@oldsalary<>@newsalary) set @AuditString = @AuditString + ' Salary from ' +CAST(@oldsalary as nvarchar(20))+ ' to ' + CAST(@newsalary as nvarchar(20)) if(@oldCity<>@newCity) set @AuditString = @AuditString + ' City from ' + @oldCity + ' to ' + @newCity if(@oldDeptId<>@newDeptId) set @AuditString = @AuditString + ' DepartmentID from ' + CAST(@oldDeptId as nvarchar(20))+ ' to ' + CAST(@newDeptId as nvarchar(20)) insert into Audit values(@AuditString) delete from #TempTable where ID = @Id End End please sir resolve this error... waiting for your response mail: [email protected]
dalila dali (1 year ago)
you are my favorite teacher,thanks a lot
Thrudvang p (1 year ago)
I have a test tomorrow and I'm watching your videos. You explain everything really good so thank you very much!
Rezoan Hasib (1 year ago)
The code looks complicated, many would have made it so while explaining but you didn't. Cuz you a legend! Hats off dude.
Joseph Regallis (2 years ago)
I wish you would have told me to change AuditData column size to nvarchar(1000) in the tblEmployeeAudit table. I kept getting errors and the AuditData string kept getting cut off. I finally saw where I had to change the column size from 70 characters to 1000. No more errors after that.
Sheogorath MadGod (2 years ago)
i have a question....i don't understand why we use "delete from #temptable where [email protected]". as far as i know, the while loop will let us insert single record on its first cycle and on the next cycle the cursor will move to next record.which will be insert to audit table.so at each cycle of while loop a record from #temptable will be added to audit table.And at the end the cursor of while will reach on an empty record hence giving 0 to while condition. but if at the end of while loop you delete the #temptable,how the while loop is suppose to re-enter another record.,at next cycle the while loop will not find #temptable.anyway your query is right and the while goes to infinite loop if not use this delete statement
Sheogorath MadGod (2 years ago)
oops sorry.... you are deleting only an id.every cycle..and there is no shifting to another record....
MixTape (2 years ago)
For everyone, try using 1.25 speed, it's understandable and you have better concentration
taimoor raza (2 years ago)
I am trying hard but it give me an error Conversion failed when converting the nvarchar value 'new member id=7date isApr 4 2017 12:22AM' to data type int.
taimoor raza (2 years ago)
Which data type use in Audit column....
Hemant Singh Bohra (2 years ago)
Error in Update Trigger Msg 245, Level 16, State 1, Procedure tr_tblEmp_forUpdate, Line 36 [Batch Start Line 47] Conversion failed when converting the nvarchar value 'Emp with Id = 3changedName from GauravtoNidhiGender from MtoFSalary from ' to data type int.
Hemant Singh Bohra (2 years ago)
Thanks. How to add username field in Audit table?
Dev Expost (1 year ago)
Something like... DROP Table tblAudit CREATE TABLE tblAudit ( MessageId INT IDENTITY(1,1) PRIMARY KEY NOT NULL, EventTime DATETIME NOT NULL, TableName VARCHAR(20), UserId VARCHAR(50) NOT NULL, AuditMessage VARCHAR(1000) NOT NULL ) INSERT INTO tblAudit VALUES ( CURRENT_TIMESTAMP, 'tblEmployee', USER_NAME(), 'Record ID=' + CAST(@Id1 AS VARCHAR(6)) + ' ', + @AuditString)
Mahebunissa Sk (2 years ago)
hi.. i had done perfectly with after triggers ( insert,delete).. when update comes i got one table ( tblemployee) updates data,,,but tblemployeeaudit table doesnt showing the data.. plz anybody clarify my doubt..
Krzysztof S (2 years ago)
Bravo! These videos are very useful. Very long syntax, then longest code from the beginning of the course.
Daniel W (2 years ago)
I'm noticing a lot of sex change in these tutorials, haha!
Bimal Das (2 years ago)
thank you sir
Rahul Tiwari (2 years ago)
Ak Number sir , what a way to Teach
Sathish Kumar (2 years ago)
Hello Sir, I have one table with the field of Slno,Period,Total and 100 rows...now i want to make a calculation (Pivot) like Slno,Period,Total(sum of month wise) in second table...Please let me know how to do this...
Jade Swanson (2 years ago)
you left out the is deleted section of the code :(
Stephen Nguyen (2 years ago)
Quick question. I'm following through the video (started from vid 1, thank you!) and I assumed you created tblEmployeeAudit with the ID column as int, NOT NULL, IDENTITY(1,1), PRIMARY KEY because you're not supplying the ID in your trigger. However, after I created your update,insert, and delete trigger (from the last video), say if I add a record into tblEmployee (maybe ID of the new record being added to tblEmployee = 9) and since the tblEmployeeAudit is empty with IDENTITY(1,1), SQL Server will attempt to add ID = 9 to tblAudit. This then generates an error "An explicit value for the identity column in table 'tblAudit' can only be specified when a column list is used and IDENTITY_INSERT is ON." , the same error that you discussed much earlier on when you first mentioned IDENTITY(1,1), how we have to set it off to explicitly add the ID = 9 to tblEmployeeAudit. It's not adding ID as 1 in tblEmployeeAudit How do you get past this, right now I'm completely stuck here even though I understand all the concepts you presented?
Mahebunissa Sk (2 years ago)
Stephen Nguyen I am also worrying with after trigger (update)querry.---unable to update a row in tblemployeeaudit table.. tblemployee table perfectly updating the required row..and tblemployeeaudit table not showing the --- id= 8 name updated.--..... update operation not working with audit table.. please tell me.. how u accessed..
Stephen Nguyen (2 years ago)
I didn't think anyone else would run into the same issue~
Justin A (2 years ago)
'Hi guys, I like to ask questions, then after figuring out the answer I don't post the solution for others to use.'
Stephen Nguyen (2 years ago)
I figured it out, thanks kudvenkat
javielitostanley (2 years ago)
Hey Bro!! Thank for you helpful Video!! You've already saved my life and added me a great knowing to my SQL's Skills!! Sincerity Thank you!! :D
kudvenkat (2 years ago)
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat
I didn't get where the #TempTable come from....Was it previously created? Or it gets created with the statement inside the trigger?
Shawn Englerth (1 year ago)
created on the fly if you haven't already explicitly declared it
Mayank Gupta (2 years ago)
s name suggests #Temtable is mean to be provide a way to temporary storage of the data, so that we can get the values under it and use it during our query execution. I Would suggest you should read much abt it, so that you can get clear idea about it. Also read difference between #TempTable and ##TempTable
+Liam2349 yes, I think I got it later. My doubt was when did he created the table, but now I think I understand that a temporary table can be created right with that statement
Liam2349 (2 years ago)
He is creating it inside the trigger. He makes it because the inserted table changes when the trigger inserts a new record into the audit table, so he first saves a copy of the inserted table into #TempTable to save the original data.
Sam Kommula (2 years ago)
the lengthy query which you have written at the bottom from gender to old department is not clearly seen, kindly scroll it to the right so that we can see the whole query clearly. all of your videos was good except this because the right side part of query is missing. kindly write the query again.
Yared DZ (2 years ago)
http://csharp-video-tutorials.blogspot.com You can access all his videos slides in this link.
sairoj alam (3 years ago)
Please asp.net listview control video send and work on proper like insert update delete every type like sqldatasource,sqldataobject and without object
Furqan butt (3 years ago)
Part 43!!!!!!!!!!!
Igor Kovac (3 years ago)
Very nice :)
Sisay Sarke (3 years ago)
Thank you for bringing the technology so close to us and make it as easy as possible for people like me to understand it. I have one question though, Is there any way to obtain the scripts in a text form? thank you again.
Yared DZ (2 years ago)
http://csharp-video-tutorials.blogspot.com If you go to the link above, you can access all slide with the script.
Gaureesh A (3 years ago)
Can we directly use inserted table in the while condition? Why do we need the temporary table?
deboadeyemi (7 months ago)
I was thinking the same thing. Maybe because you have to delete each row at the end of the trigger to prevent infinite loop. It may not be best practice or impossible to delete rows in inserted since it's a automatically produced record.
Ryan Blais (3 years ago)
Having problems with the triggers on this video and previous. Says that column names don't match or "Invalid Column name". Have checked and re-checked my code to make sure it matches with that on your blog. Are my settings wrong in MSSMS or do I need to go into the Config Manager or something else?
Anil Bhandari (2 years ago)
I think u made mistake on insert into tblemployeeAudit. this table takes only two value.
DEVEN MHATRE (2 years ago)
Msg 213, Level 16, State 1, Procedure tr_tblEmployee1_forDelete, Line 9 Column name or number of supplied values does not match table definition. the error is shown at place where we concat the string to display it on the AuditData table
Salman Shaikh (3 years ago)
+Ryan Blais copy paste the error and code here then someone can guess what's the error.
Shubham Mittal (3 years ago)
hello, I am getting an issue. when i update any record the output of the EmployeeAudit table is ( Employee with Id=2 changed) .  So, my issue is that why it shows me the output upto changed only, where is the rest of the output gone.  I had follwed the same flow as explained in the given video. plzzz help
Thế Hiển (2 years ago)
I think you have to change the length of AuditData to nvarchar(1000)
Salman Shaikh (3 years ago)
+Shubham Mittal double check these statements- Set @AuditString = @AuditString+........rest of logic if the second @AuditString is missing then u won't see the 'changed from' +'to' output.
Shanshan Li (4 years ago)
Hi Venkat, I am thinking whether we could put the 'insert into tblEmployeeAudit values(@AuditString)' line within each if case analysis. That way if someone is trying to update multiple column values ( say name, gender AND salary) at one shot, the audit table can catch everything instead of the last value stored in the variable @AuditString. Please let me know if I miss anything.  Btw you have been very helpful on my way learning SQL. Very concise and informative tutorials. Thanks a lot;-)
Ammar Rasras (4 years ago)
Mr Venkat, what about this message after insert trigger  "cannot insert explicit value for identity column in Audit table when identity_insert is set to off. 
Salman Shaikh (3 years ago)
+عمار رصرص your welcome :)
Ammar Rasras (3 years ago)
thank you very much 
Salman Shaikh (3 years ago)
+‫عمار رصرص‬‎ that means you should not insert id values in id column, that will be done automatically.
Felix Antony (4 years ago)
I have one doubt. If we update a table, will it call the update trigger... Because, this trigger is update event triggered one. Then how can we identify whether this triggering occurred  at update or Delete event??? 
#Stuti# #Tehri# (4 years ago)
great videos
Robbin Shee (5 years ago)
You are a life saver !!!!
Jyothi Krishna (5 years ago)
+Surampudi Tpdevi  ..I think you didn set the Id column as identity column thats why you get this error...
Surampudi Tpdevi (5 years ago)
hi venkat, i am getting this "Column name or number of supplied values does not match table definition." ? while creating the trigger
kudvenkat (5 years ago)
Hi Ravi, To receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. May I ask you for a favour. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video.
kudvenkat (5 years ago)
Hi Ravi, you are absolutely right. The temp tables get automatically dropped after the stored proc has completed execution. That is the default behaviour. However, I personally feel, it's a good coding practice to explicitly drop the temp tables, than to leave them for the sql server to drop. This makes your code more readable, and may free up the memory that the temp table is holding onto, early in the process than waiting until the stored procedure is completed. Hope this answers your question
Ravi Yadav (5 years ago)
hello sir, in this video you delete temp table, but i have learned that any of the temporary table created by trigger deleted automatically when proc execution completed , so is it required to delete it explicitly , please explain.
Frank Djabanor (5 years ago)
Venkat.. You have a unique gift of explaining complicated material so it is very easy to understand. I thank you for helping me pass my 70-461 Exam!
Mahdy Moustafa (6 years ago)
Man, you deserve Heaven & I wish it for you :-)
kudvenkat (6 years ago)
Hi Tam, I have included the link to my blog in the description of this video, which contains the code you are looking for. Hope you will find it useful.
Mahdy Moustafa (6 years ago)
Dear Venkat, Can I get this code from you? Thank you Sir.
Anurag Vashishtha (6 years ago)
Understanding the concept of an Update Trigger was never easy. The way you have built the foundation of this topic by explaining the significance of "inserted" and "deleted" tables "inside the scope of the Trigger" is a Master Stroke. I have said this before and I am compelled to say it one more time that you know the Science of Logic Implementation and the art of Teaching. Thanks a ton for these awesome video series.
Merin Nakarmi (6 years ago)
Awesome.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.