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

Instead of insert trigger Part 45

394 ratings | 148688 views
In this video we will learn using instead of insert trigger to insert a row into a view that is based on multiple tables. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/instead-of-insert-trigger-part-45.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-45-instead-of-insert-trigger.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 (39)
Sohan Yennu (1 year ago)
I really enjoyed this video...very informative
kudvenkat (1 year ago)
Hello Dear Sohan, 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
tony fdd (1 month ago)
Hi sir , One small doubt In that trigger why do v need that last line I.e select Id,name,gender,@depid from inserted
Tech Durgesh (8 months ago)
Sir, I have a problem. I want to get the record from TABLE _1 and update the table _2 and the original tbale (TABLE _1) I have no updates and updated record table _2 I get inserted.
Dharam Shakya (10 months ago)
please make on partition in sql
Shabbir Ali (11 months ago)
I m learning SQL very well from yours tutorial :) I have a small doubt e.g you create a int variable and join depname between tables and put these into Int variable Why? plz explain
Renganathan SRB (1 year ago)
You are awesome, your teaching is very soft and nice. Thanks for ur videos.
harsha vardhan (1 year ago)
Hi Venkat, While we are trying to check the user supplied DepartId (@DeptId) with the DepartmentId in the table, what is the reason that we are using '=' operator instead of '==' operator. Please clarify.
sumit bhardwaj (1 year ago)
i like your all video, and thank those friend whose tolled me, watch prazym technology videos your video is too long but u difine many things in detail if any body have no knowledge about programing language your video is very helpful of those people india proud of your Mr. kudvenkat u i am very happy of your work and u give free teach in good quality every one not gud in english so u should one copy of hindi language
Chandrabhan Bhardwaj (1 year ago)
hell sir.......................is it possible to return value from trigger to c#......if yess then how? ......... and if no then Why?
Dev Expost (1 year ago)
It's a fine example for a tutorial, but note that the trigger assumes the DeptName column is unique, and also that there is only a single row insert being performed.
Jay Du (1 year ago)
I am just curious that in the real world, do we write triggers for audit or the sql server environment provides auditing features? Thanks!
Sayali Thakar (1 year ago)
Hi Venkat,very useful video.Please put videos on Datastructures and Algorithm
bext la (1 year ago)
Hi. it is useful. when l insert new values to view table, l face with problem that 'Conversion failed when converting the nvarchar value 'IT' to data type int'. How can l fix this?
Krzysztof S (1 year ago)
very useful videos. i'm going to end the whole course. till now they quality is on the high level
Hussain Madarwala (1 year ago)
+kudvenkat when I create a trigger ,it inserts the columns in inserted but cannot insert it into base tables?
Mohammad Shawn (2 years ago)
nice
Rajon Islam (2 years ago)
I am getting on this video. Please HELP Part 45: Alter Trigger tr_vWEmployeeDetailsInsteadOfTrigger_InsteadOfInsert On vWEmployeeDetailsInsteadOfTrigger Instead Of Insert As Begin Declare @DeptID int -- creating a variable to hold the value of DepartmentID as integer. -- Check if there is a valid DepartmentID -- For the given DepartmentName Select @DeptID = DeptID From tblDepartmentInsteadOfTrigger Join inserted On inserted.DeptName = tblDepartmentInsteadOfTrigger.DeptName -- If DepartmentID is Null throw an error -- And stop processing If(@DeptID is Null) Begin Raiserror('Invalid Department Name. Statement terminated', 16, 1) Return End -- Finally insert into tblEmployeeInsteadOfTrigger table Insert into tblEmployeeInsteadOfTrigger(ID, Name, Gender, DepartmentID) Select ID, Name, Gender, @DeptID End Error: Msg 207, Level 16, State 1, Procedure tr_vWEmployeeDetailsInsteadOfTrigger_InsteadOfInsert, Line 25 Invalid column name 'ID'. Msg 207, Level 16, State 1, Procedure tr_vWEmployeeDetailsInsteadOfTrigger_InsteadOfInsert, Line 25 Invalid column name 'Name'. Msg 207, Level 16, State 1, Procedure tr_vWEmployeeDetailsInsteadOfTrigger_InsteadOfInsert, Line 25 Invalid column name 'Gender'.
Hussain Madarwala (1 year ago)
check your tblEmployeeInsteadOfTrigger columns properly
Shweta Gadia (2 years ago)
Hello sir, can you please suggest best way to insert multiple rows using instead of inert trigger. Example provided in the video works fine when query insert only 1 row at a time. But it failed when I tried inserting multiple rows. I also tried one by using cursor, which worked as expected but adversely hit query performance. Any suggestion will be of great help.
Why having "id" column as identity gives me 0 in "select * from inserted".
Salman Shaikh (2 years ago)
I have a small doubt. Initially when u created the trigger as select * from inserted. Then insert into view statement and execute the statement. But no data gets actually inserted in the view even though the trigger fires. So how is possible that the select * from inserted returns the row that we were trying to insert???
Cascade Aklilu (2 years ago)
thank you sir
Cascade Aklilu (2 years ago)
hi I got a lot from this and old other lesson. cld u advise me where to star in programing tnx
kudvenkat (2 years ago)
+Cascade Aklilu 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 The following is the order 1. C# 2. Dot Net Basics 3. SQL Server 4. ADO .NET 5. ASP .NET 6. GridView tutorial 7. MVC 8. WCF 9. Entity Framework 10. LINQ 11. LINQ to XML 12. LINQ to SQL 13. Chart Control 14. JavaScript 15. JavaScript with ASP .NET 16. jQuery 17. AngularJS If you need DVDs 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
Red Duck (3 years ago)
Excelent videos
shrawan kumar chavan (4 years ago)
Hi Venket, Thanks for ur videos. I have a small doubt regarding raise error thing. When u tried to insert invalid department name in the view there was an error saying "Invalid Department name. Statement terminated". When nothing is executed after this error, then y we get "1 row(s) affected" msg below the error msg?
Revics (4 years ago)
You made the view only support single row inserts by using the int variable. I would have used: "insert into ... select ... from inserted inner join tblDepartment on ...".
Felix Antony (4 years ago)
Very useful. Thank you master...
Pramod Kumar Sura (4 years ago)
Hello Venkat, You are awesome, your teaching is very soft and nice.  a layman can understand the concept easily. 
Richard Lah (4 years ago)
HI Kudvenkat I am thro with the sql server which was excellent. My interested is  heightened in the other 2 courses i.e C# and ASP.Net . Can you advise on the effective order to study these 2. Should it be C# first or Asp.net. Many Thanks Richard
kudvenkat (4 years ago)
Please start with C#, followed by ASP.NET. Good Luck.
#Stuti# #Tehri# (4 years ago)
god bless you;sir
Mostafa abd elnasser (4 years ago)
you hitting the target soo good 
danletmedown (5 years ago)
please reply to me teacher so that I can receive an email notification... im eager for what you can suggest :)
kudvenkat (5 years ago)
Thank you very much for taking time to give feedback. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video. For email alerts, when new videos are uploaded, you may subscribe to my channel.
cassavates (5 years ago)
Hi Venkat, let me thank you very much for this thorough and detailed and beginner-friendly training - it is not even on my mother-tongue and still I understand also the complex stuff I thought I might not understand :) Also it teaches how to be structured for SQL thinking, and for my DB-s consistency and unambiguity. Great training, thanks and congratulations, and I wish a lot of success with your work for the future! Sincerely: cassavates
sharma amit (5 years ago)
i learn sql or asp.net from your vedio ,u r the best teacher
Elaine Mayer (5 years ago)
I've watched over 30 of your sql training videos. They're all very helpful. Thank you so much for taking time to make these videos.

Would you like to comment?

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