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 th-cam.com/users/kudvenkatplaylists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. th-cam.com/video/y780MwhY70s/w-d-xo.html If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. th-cam.com/users/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
@@Csharp-video-tutorialsBlogspot looking to buy all the videos and everything you have for like 170$ right? since i be working on big project to get a certificate of programming engineer i needed your videos to help me me remembering all the stuff i learned i been looking through many books and pdfs nothing help more then your videos thanks for them i might buy everything very soon its very helpful and saves a lot of time searching for resources to learn i just click on the video and just learn i don't have to worry if its all the resources i need or i missed something out or i cant find that and that i just want to click and learn everything without worrying about anything else thank you *i have a question does the DVD version comes with one disc Blu-ray or anything or is it like many DVDs ? and not on one DVD only thanks*
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.
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
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 th-cam.com/users/kudvenkatplaylists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. th-cam.com/video/y780MwhY70s/w-d-xo.html If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. th-cam.com/users/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
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.
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.
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.
When you create a trigger for 'after trigger' to insert a data you have to write 'select * from inserted', and when you create an 'after trigger' to delete a record, you have to write 'select * from delete'. This internally creates a copy of actual table in which you have inserted your records. When you execute a trigger, the data from actual table is fetched and the results are affected in inserted or deleted (virtually created tables) tables.
Dear Venkat: I created this trigger using different names so that I can understand it better intentionally. My code does not match but does work if I just use [ delete from #TempTable ] instead of {xx} below, this means loop never happens, but I get error while copiling at: {delete from #TempTable WEHRE EmpId = @Id}. If I select * from #TempTable right before the above statement, Then I can see the EmpId is there. Some how I get compilation error as below telling me that EmpId is invalid column name when I park my mouse on it. "Msg 102, Level 15, State 1, Procedure tr_AuditorEmployee_Update, Line 42 [Batch Start Line 10] Incorrect syntax near 'WEHRE'.." here is my code if you want to look at it. ALTER TRIGGER tr_AuditorEmployee_Update ON tEmployee FOR UPDATE AS BEGIN DECLARE @Id int DECLARE @Name varchar(20) DECLARE @Salary Int DECLARE @Gender varchar(20) DECLARE @DeptId int DECLARE @NewEmpId int DECLARE @NewName varchar(20) DECLARE @NewSalary Int DECLARE @NewGender varchar(20) DECLARE @NewDeptId int DECLARE @AuditString varchar(2000) SELECT * INTO #TempTable FROM inserted WHILE (EXISTS(SELECT EmpId From #TempTable)) BEGIN SET @AuditString = '' --New SELECT TOP 1 @Id = EmpId, @NewName = Name, @NewSalary = Salary, @NewGender = Gender, @NewDeptId = DeptId FROM #TempTable -- Old SELECT @Name = Name, @Salary = Salary, @Gender = Gender, @DeptId = DeptId FROM deleted WHERE EmpId = @Id SET @AuditString = 'Upadtes: ID:' + CAST(@Id AS nvarchar(20)) + ' ' --SELECT @Name AS OONE, @NewName AS TWOO If (@Name@NewName) Set @AuditString = @AuditString + @Name + ' Updated to: ' + @NewName + ' ~' If (@Salary@NewSalary) Set @AuditString = @AuditString + CAST( @Salary as nvarchar(20)) + ' Updated to: ' + CAST( @NewSalary as nvarchar(20)) + ' ~' If (@Gender@NewGender) Set @AuditString = @AuditString + @Gender + ' Updated to: ' + @NewGender + ' ~' If (@DeptId@NewDeptId) Set @AuditString = @AuditString + CAST( @DeptId as nvarchar(20)) + ' Updated to: ' + CAST( @NewDeptId as nvarchar(20)) + ' ~' INSERT INTO tEmployeeAudit VALUES(@EmpId, @AuditString, Getdate()) --SELECT *, 'TempTable' FROM #TempTable delete from #TempTable WEHRE EmpId = @Id END END I can't figure out what I cant use "WHERE EmpId = @Id" at the delete statement to continue the loop? Any one who can help will be appreciated. Thanks I am so happy by going through this series. I can not thank you enough.
Hi Sir, I tried to follow query in this video and it's going well if i updated just one column, but it's going to infnite loop if I try to update mulitples column. Example update tblemployee set departmentid = 3 where id in (2,3,4) any suggestion ? thank you.
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.
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;-)
Before I try this hands-on, in situations where you have multiple records changing, if ID in (1,3,5) was used instead of ID = 4 will the TempTable get the IDs sequentially? is that why you used Top 1? Another question is the TempTable you used, it was not created before it was used in your query, can you insert in a temporary table that has not been created before the time of use, or is TempTable a system temporary table?
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.
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?
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..
One case that you may have ve forgotten: when any column variable value is NULL. You can't compare NULL values using or = although this is not the point on which your lecture focused .
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?
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.
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...
i have a question....i don't understand why we use "delete from #temptable where id=@id". 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
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
my update and insert command is executed like 'Employee with Id = 4 changed and New Employee with Id = 6 is added', It's not showing whole Employee's details, I used same query. Please answer me. Thanks!
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.
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???
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?
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
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.
Hi Venkat, Thanks for your great videos and effort in doing such a great work. My question is while creating a trigger I got an error like infinite while loop, after resolved it is working fine as it was. But auditdata table id has changed, it is showing after loop numbering, as I mentioned id as an identity column. Is there any way to get back to the serial number? The ids are like this 4 5 6 7 8 655556 655557 655558 Thanks in advance.
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.
+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
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
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.
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.
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.
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
+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.
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..
Presumbly it is due to the read-only nature of the INSERTED table. That's why you have to copy the whole INSERTED table into a temporary table so that every processed row could be deleted.
you can not delete data directly from the inserted table that`s why we need to copy it into another table which is nothing but a temp table So now you can delete data from the temp table So I hope your all doubts are cleared. thank you .
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.
![[Live] : ONE 168 วันนี้!! "โจนาธาน vs ซุปเปอร์เล็ก"](http://i.ytimg.com/vi/0jIN65KgJsI/mqdefault.jpg)
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..
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
th-cam.com/users/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/kudvenkat_dvd.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
th-cam.com/video/y780MwhY70s/w-d-xo.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
th-cam.com/users/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
Its more then useful. Its an asset
@@Csharp-video-tutorialsBlogspot looking to buy all the videos and everything you have for like 170$ right? since i be working on big project to get a certificate of programming engineer i needed your videos to help me me remembering all the stuff i learned i been looking through many books and pdfs nothing help more then your videos thanks for them i might buy everything very soon its very helpful and saves a lot of time searching for resources to learn i just click on the video and just learn i don't have to worry if its all the resources i need or i missed something out or i cant find that and that i just want to click and learn everything without worrying about anything else thank you
*i have a question does the DVD version comes with one disc Blu-ray or anything or is it like many DVDs ? and not on one DVD only thanks*
@@Csharp-video-tutorialsBlogspot nice👍
10 years since the playlist but still helping so many students!! Love and respect to you sir!!
Man, you deserve Heaven & I wish it for you :-)
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.
You are the reason that normal schools should be changed for online schools with people like You ! really... MY HERO!
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
I have a test tomorrow and I'm watching your videos. You explain everything really good so thank you very much!
Easy way of understanding about triggers .Thank You Venkat Sir.
The code looks complicated, many would have made it so while explaining but you didn't. Cuz you a legend! Hats off dude.
Thank You Mr Venkat, The best teacher over the world
You are born teacher just A mighty gift for all the beginar developer thank you sir
Bravo! These videos are very useful. Very long syntax, then longest code from the beginning of the course.
Superb videos! I have finally been able to understand triggers! thank you so much
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!
This video was posted 10 years ago . I AM watching it in 2023 . in 2012 i dint knew anything about sql and all. Thankyou.
Superb videos! I have finally been able to understand triggers! thank you so much Sir
I think no one else can explain as simple as you , you are an exceptional one sir, thanks a lot :)
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
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
th-cam.com/users/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/kudvenkat_dvd.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
th-cam.com/video/y780MwhY70s/w-d-xo.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
th-cam.com/users/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
The best teacher ever.
No words to say..hats off!!!!!
Thank you so much...God bless you!!! Your videos help us to learn concept deeper and clearly.
Very informative video..Thanks a lot from all the learners..Keep it up. 👍
Thank you so much. Your videos are very useful and its clearly explained
This is very helpful, i thank you very much for your effort sir, thumbs up (y) !
This is so helpful as always!
God bless you sir. Excellent teaching
you are my favorite teacher,thanks a lot
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.
Excellent video as usual. Very well done.
u r extremely super explaining sir
Ak Number sir , what a way to Teach
Very interesting example ❤️
You are a life saver !!!!
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.
Amazing explaination. Thank you
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.
When you create a trigger for 'after trigger' to insert a data you have to write 'select * from inserted', and when you create an 'after trigger' to delete a record, you have to write 'select * from delete'. This internally creates a copy of actual table in which you have inserted your records. When you execute a trigger, the data from actual table is fetched and the results are affected in inserted or deleted (virtually created tables) tables.
Dear Venkat:
I created this trigger using different names so that I can understand it better intentionally. My code does not match but does work if I just use [ delete from #TempTable ] instead of {xx} below, this means loop never happens, but I get error while copiling at:
{delete from #TempTable WEHRE EmpId = @Id}.
If I select * from #TempTable right before the above statement, Then I can see the EmpId is there.
Some how I get compilation error as below telling me that EmpId is invalid column name when I park my mouse on it.
"Msg 102, Level 15, State 1, Procedure tr_AuditorEmployee_Update, Line 42 [Batch Start Line 10]
Incorrect syntax near 'WEHRE'.."
here is my code if you want to look at it.
ALTER TRIGGER tr_AuditorEmployee_Update
ON tEmployee
FOR UPDATE
AS
BEGIN
DECLARE @Id int
DECLARE @Name varchar(20)
DECLARE @Salary Int
DECLARE @Gender varchar(20)
DECLARE @DeptId int
DECLARE @NewEmpId int
DECLARE @NewName varchar(20)
DECLARE @NewSalary Int
DECLARE @NewGender varchar(20)
DECLARE @NewDeptId int
DECLARE @AuditString varchar(2000)
SELECT * INTO #TempTable FROM inserted
WHILE (EXISTS(SELECT EmpId From #TempTable))
BEGIN
SET @AuditString = ''
--New
SELECT TOP 1 @Id = EmpId, @NewName = Name, @NewSalary = Salary, @NewGender = Gender, @NewDeptId = DeptId FROM #TempTable
-- Old
SELECT @Name = Name, @Salary = Salary, @Gender = Gender, @DeptId = DeptId FROM deleted WHERE EmpId = @Id
SET @AuditString = 'Upadtes: ID:' + CAST(@Id AS nvarchar(20)) + ' '
--SELECT @Name AS OONE, @NewName AS TWOO
If (@Name@NewName) Set @AuditString = @AuditString + @Name + ' Updated to: ' + @NewName + ' ~'
If (@Salary@NewSalary) Set @AuditString = @AuditString + CAST( @Salary as nvarchar(20)) + ' Updated to: ' + CAST( @NewSalary as nvarchar(20)) + ' ~'
If (@Gender@NewGender) Set @AuditString = @AuditString + @Gender + ' Updated to: ' + @NewGender + ' ~'
If (@DeptId@NewDeptId) Set @AuditString = @AuditString + CAST( @DeptId as nvarchar(20)) + ' Updated to: ' + CAST( @NewDeptId as nvarchar(20)) + ' ~'
INSERT INTO tEmployeeAudit VALUES(@EmpId, @AuditString, Getdate())
--SELECT *, 'TempTable' FROM #TempTable
delete from #TempTable WEHRE EmpId = @Id
END
END
I can't figure out what I cant use "WHERE EmpId = @Id" at the delete statement to continue the loop?
Any one who can help will be appreciated. Thanks
I am so happy by going through this series. I can not thank you enough.
just amazing.
For everyone, try using 1.25 speed, it's understandable and you have better concentration
Listening at 1.75 x would be much better
Hi, 1:14, part 42 of the video series is view limitations. not triggers.
BTW, you're an awesome teacher.
Hi Sir,
I tried to follow query in this video and it's going well if i updated just one column, but it's going to infnite loop if I try to update mulitples column. Example update tblemployee set departmentid = 3 where id in (2,3,4)
any suggestion ? thank you.
Thanks for your knowledge sharing..
Wow...I am a fan
Hm. What if I don't change anything in the update statement? I will enter garbage text in the tblEmployeeAudit.
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.
csharp-video-tutorials.blogspot.com
If you go to the link above, you can access all slide with the script.
it is very useful thanks
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;-)
hello sir,in this video,your mention slide-42(DML Trriger),but actualy slide-42 is (View limitations concept).
Thank you guruji
awesome tutorial
Before I try this hands-on, in situations where you have multiple records changing, if ID in (1,3,5) was used instead of ID = 4 will the TempTable get the IDs sequentially? is that why you used Top 1? Another question is the TempTable you used, it was not created before it was used in your query, can you insert in a temporary table that has not been created before the time of use, or is TempTable a system temporary table?
Thank you!!!
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.
Why not use inserted table for while loop? Doesn'tt it contain multiple rows?
Thank you sir.
Awesome.
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?
I figured it out, thanks kudvenkat
'Hi guys, I like to ask questions, then after figuring out the answer I don't post the solution for others to use.'
I didn't think anyone else would run into the same issue~
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..
Very nice :)
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
Should we join inserted table and the table we use to get data from ?
One case that you may have ve forgotten: when any column variable value is NULL. You can't compare NULL values using or = although this is not the point on which your lecture focused .
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?
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.
for inserted why we are using a separate temp table. is there any purpose for this
hi venkat,
i am getting this "Column name or number of supplied values does not match table definition."
? while creating the trigger
Same problem, it didn't take id value Automatically that's why..
when we are using top 1 from temptable isnt that will be the same row we are always selecting?
okay got it. we are deleting that row at the end
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...
Which data type use in Audit column....
i have a question....i don't understand why we use "delete from #temptable where id=@id". 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
oops sorry.... you are deleting only an id.every cycle..and there is no shifting to another record....
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
sir, how get/insert end user id/name into audit table using Update trigger when i using c#
my update and insert command is executed like 'Employee with Id = 4 changed and New Employee with Id = 6 is added', It's not showing whole Employee's details, I used same query. Please answer me. Thanks!
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.
very hard example for beginners
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???
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?
+Ryan Blais copy paste the error and code here then someone can guess what's the error.
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
I think u made mistake on
insert into tblemployeeAudit. this table takes only two value.
please may you tell why loop become infinite loop if i not delete id from #temptable
Can we directly use inserted table in the while condition? Why do we need the temporary table?
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.
you can't remove rows from inserted table
Hello Sir, hope you are fine! Can we get the slides as well??
Hi Venkat, Thanks for your great videos and effort in doing such a great work. My question is while creating a trigger I got an error like infinite while loop, after resolved it is working fine as it was. But auditdata table id has changed, it is showing after loop numbering, as I mentioned id as an identity column. Is there any way to get back to the serial number? The ids are like this
4
5
6
7
8
655556
655557
655558
Thanks in advance.
I didn't get where the #TempTable come from....Was it previously created? Or it gets created with the statement inside the trigger?
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.
+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
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
created on the fly if you haven't already explicitly declared it
you left out the is deleted section of the code :(
Where will deleted row go when we delete it from deleted table?
Why did salary increase when you changed gender from female to male at 3:30?
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.
Surampudi Tpdevi ..I think you didn set the Id column as identity column thats why you get this error...
can anyone help me how he create audit table with what data type
What is the difference between part 43 and part 44..I can't found any.
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.
+عمار رصرص that means you should not insert id values in id column, that will be done automatically.
thank you very much
عمار رصرص your welcome :)
Thanks.
How to add username field in Audit table?
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)
@@devexpost8508 USER_NAME()??
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.
awesome sir amazing
Super sir
I think using varibles is too complicated. You should use insert into ''''''' select ''''' from inserted/deleted
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
+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.
I think you have to change the length of AuditData to nvarchar(1000)
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..
ctrl v your code here
Please asp.net listview control video send and work on proper like insert update delete every type like sqldatasource,sqldataobject and without object
I am still confused why u have created temp table. I tried the same program but without temp table, I got error. I can't understand the logic
Presumbly it is due to the read-only nature of the INSERTED table. That's why you have to copy the whole INSERTED table into a temporary table so that every processed row could be deleted.
you can not delete data directly from the inserted table that`s why we need to copy it into another table which is nothing but a temp table So now you can delete data from the temp table So I hope your all doubts are cleared. thank you .
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.
csharp-video-tutorials.blogspot.com
You can access all his videos slides in this link.
loop taking much time to be executed mean i couldn't get see result its not being executed