title
Instead of insert trigger Part 45
description
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
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1
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
detail
{'title': 'Instead of insert trigger Part 45', 'heatmap': [{'end': 425.589, 'start': 415.621, 'weight': 1}, {'end': 514.457, 'start': 484.999, 'weight': 0.77}], 'summary': "Covers instead of insert triggers in sql server, explaining their usage for updating views based on multiple tables and addressing the error of modification affecting multiple base tables, with a demonstration of creating a view and an instead of insert trigger. it also discusses the usage of inserted and deleted tables in triggers, creation of 'instead of' triggers, and execution of insert into view queries. the implementation of sql server triggers for error handling and instead of insert trigger is also explained, covering the use of raiseerror function, 'inserted' and 'deleted' tables, and foreign key violations.", 'chapters': [{'end': 359.847, 'segs': [{'end': 66.742, 'src': 'embed', 'start': 0.009, 'weight': 0, 'content': [{'end': 1.87, 'text': 'Hello, welcome to Prezim Technologies.', 'start': 0.009, 'duration': 1.861}, {'end': 2.771, 'text': 'I am Venkat.', 'start': 2.21, 'duration': 0.561}, {'end': 5.092, 'text': 'This is part 45 of SQL Server.', 'start': 3.031, 'duration': 2.061}, {'end': 8.054, 'text': "In this session, we'll learn about instead of triggers.", 'start': 5.552, 'duration': 2.502}, {'end': 11.416, 'text': "Specifically, we'll be talking about instead of insert trigger.", 'start': 8.394, 'duration': 3.022}, {'end': 17.459, 'text': 'Before continuing with the session, I strongly recommend to watch parts 43 and 44 of this video series.', 'start': 11.816, 'duration': 5.643}, {'end': 23.523, 'text': 'In SQL Server, there are three types of triggers, DML, DDL, and logon triggers.', 'start': 18.94, 'duration': 4.583}, {'end': 31.223, 'text': 'DML triggers are fired automatically in response to DML events Examples of DML events include insert, update, and delete.', 'start': 24.003, 'duration': 7.22}, {'end': 37.287, 'text': 'DML triggers can be further classified into two types, after triggers and instead of triggers.', 'start': 31.884, 'duration': 5.403}, {'end': 44.532, 'text': 'After triggers are fired after the triggering action, whereas instead of triggers are fired instead of the triggering action.', 'start': 37.748, 'duration': 6.784}, {'end': 46.153, 'text': 'The name itself suggests that.', 'start': 44.612, 'duration': 1.541}, {'end': 50.216, 'text': "So in this session, we'll be talking about instead of triggers.", 'start': 47.695, 'duration': 2.521}, {'end': 55.52, 'text': "Let's instead of insert triggers specifically, let's try to understand that with an example.", 'start': 50.557, 'duration': 4.963}, {'end': 64.221, 'text': 'I have two tables here tblEmployee on the left hand side, which has got id name, gender and department id columns, and tblDepartment,', 'start': 55.899, 'duration': 8.322}, {'end': 66.742, 'text': 'which has got department id and department name columns.', 'start': 64.221, 'duration': 2.521}], 'summary': 'Sql server session on instead of insert triggers, part 45, covers dml triggers and their types.', 'duration': 66.733, 'max_score': 0.009, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo9.jpg'}], 'start': 0.009, 'title': 'Instead of insert triggers in sql server', 'summary': 'Covers the concept of instead of insert triggers in sql server, explaining their usage for updating views based on multiple tables and addressing the error of modification affecting multiple base tables, with a demonstration of creating a view and an instead of insert trigger to correctly insert data into the underlying base table.', 'chapters': [{'end': 359.847, 'start': 0.009, 'title': 'Sql server: instead of insert triggers', 'summary': 'Covers the concept of instead of insert triggers in sql server, explaining their usage for updating views based on multiple tables and addressing the error of modification affecting multiple base tables, with a demonstration of creating a view and an instead of insert trigger to correctly insert data into the underlying base table.', 'duration': 359.838, 'highlights': ['Instead of triggers are fired instead of the triggering action, and are used to update views correctly that are based on multiple tables.', 'Demonstration of creating a view based on multiple tables and encountering the error of modification affecting multiple base tables when trying to insert data into the view.', 'Process of creating an instead of insert trigger to correctly insert data into the underlying base table, addressing the error of modification affecting multiple base tables.']}], 'duration': 359.838, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo9.jpg', 'highlights': ['Instead of triggers are fired instead of the triggering action, used to update views based on multiple tables.', 'Process of creating an instead of insert trigger to correctly insert data into the underlying base table.', 'Demonstration of creating a view based on multiple tables and encountering the error of modification affecting multiple base tables.']}, {'end': 573.503, 'segs': [{'end': 432.917, 'src': 'heatmap', 'start': 415.621, 'weight': 0, 'content': [{'end': 420.105, 'text': 'instead of inserting this row into the view, it actually executes this trigger.', 'start': 415.621, 'duration': 4.484}, {'end': 425.589, 'text': "And in this trigger you're told to select the rows from inserted and deleted tables and that's what it will do.", 'start': 420.525, 'duration': 5.064}, {'end': 427.07, 'text': "So let's try to do that.", 'start': 425.869, 'duration': 1.201}, {'end': 428.591, 'text': "So now we shouldn't get an error.", 'start': 427.21, 'duration': 1.381}, {'end': 432.917, 'text': 'instead it will select the rows from inserted table and deleted table.', 'start': 429.176, 'duration': 3.741}], 'summary': 'Trigger selects rows from inserted and deleted tables, resolving errors.', 'duration': 72.669, 'max_score': 415.621, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo415621.jpg'}, {'end': 520.421, 'src': 'heatmap', 'start': 483.158, 'weight': 1, 'content': [{'end': 484.999, 'text': 'But then, behind the scenes, within the trigger.', 'start': 483.158, 'duration': 1.841}, {'end': 498.946, 'text': 'what you need to do is take this department name from this table and then retrieve the department ID from TBL department table and then insert that department ID,', 'start': 484.999, 'duration': 13.947}, {'end': 502.168, 'text': 'along with ID, name and gender, into TBL employee table.', 'start': 498.946, 'duration': 3.222}, {'end': 503.509, 'text': "So let's see the trigger.", 'start': 502.388, 'duration': 1.121}, {'end': 506.491, 'text': 'So we have this trigger here.', 'start': 505.21, 'duration': 1.281}, {'end': 514.457, 'text': 'So if you look at the implementation of the trigger, so we are creating this trigger on the view instead of insert as begin.', 'start': 507.472, 'duration': 6.985}, {'end': 520.421, 'text': 'So first we are creating a variable to hold the department ID, so which is of type integer.', 'start': 514.797, 'duration': 5.624}], 'summary': 'Trigger retrieves department id and inserts it into employee table.', 'duration': 37.263, 'max_score': 483.158, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo483158.jpg'}, {'end': 559.415, 'src': 'embed', 'start': 530.4, 'weight': 5, 'content': [{'end': 535.643, 'text': 'and we have spoken about joins in a great detail in the previous sessions of this video series.', 'start': 530.4, 'duration': 5.243}, {'end': 537.904, 'text': 'so please check them if you are new to joins.', 'start': 535.643, 'duration': 2.261}, {'end': 543.387, 'text': 'so we are joining that with inserted table on the department name.', 'start': 537.904, 'duration': 5.483}, {'end': 552.111, 'text': 'because, look at this, you have the inserted table here and then in the department table you have department name and department ID.', 'start': 543.387, 'duration': 8.724}, {'end': 559.415, 'text': "so obviously you're joining this inserted table on department name and getting the department ID from TBL department.", 'start': 552.111, 'duration': 7.304}], 'summary': 'Detailed explanation of joining inserted table on department name.', 'duration': 29.015, 'max_score': 530.4, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo530400.jpg'}], 'start': 360.248, 'title': 'Triggers, tables, and inserts', 'summary': "Covers the usage of inserted and deleted tables in triggers, creation of 'instead of' triggers, and execution of insert into view queries, along with updates to the tblemployeebase table using instead of insert triggers.", 'chapters': [{'end': 415.621, 'start': 360.248, 'title': 'Understanding triggers and special tables', 'summary': "Explains the usage of inserted and deleted tables in triggers, highlighting their role in containing newly added and deleted data, and mentions the creation of an 'instead of' trigger.", 'duration': 55.373, 'highlights': ["The 'inserted' table contains newly added data, while the 'deleted' table contains deleted rows.", 'The inserted table contains new data in the case of an update, while the deleted table contains the old data.', "Mentions the creation of an 'instead of' trigger on a view for handling insert statements."]}, {'end': 458.179, 'start': 415.621, 'title': 'Instead of insert trigger', 'summary': 'Explains the creation of an instead of insert trigger that selects rows from inserted and deleted tables to update the underlying tblemployeebase table.', 'duration': 42.558, 'highlights': ['The trigger selects the rows from inserted and deleted tables to update the underlying tblEmployeeBase table.', 'The inserted table contains the ID, name, gender, and department name of the inserted row.', 'The deleted table will be empty in this scenario as no row was deleted from the view.']}, {'end': 573.503, 'start': 458.46, 'title': 'Executing insert into view query', 'summary': 'Discusses the execution of an insert into view query where an employee is inserted into a table with id 7, name valerie, and gender female, along with the process of retrieving the department id and inserting it into the tbl employee table using a trigger.', 'duration': 115.043, 'highlights': ['An employee is being inserted into tblEmployee table with ID 7, name Valerie, and gender as female, along with the department name.', 'The trigger retrieves the department ID from TBL department table and inserts it along with ID, name, and gender into TBL employee table.', 'The trigger creates a variable to hold the department ID, selects the department ID from tbl department table, and joins it with the inserted table based on the department name.']}], 'duration': 213.255, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo360248.jpg', 'highlights': ['The trigger retrieves the department ID from TBL department table and inserts it along with ID, name, and gender into TBL employee table.', 'An employee is being inserted into tblEmployee table with ID 7, name Valerie, and gender as female, along with the department name.', 'The trigger creates a variable to hold the department ID, selects the department ID from tbl department table, and joins it with the inserted table based on the department name.', 'The trigger selects the rows from inserted and deleted tables to update the underlying tblEmployeeBase table.', "Mentions the creation of an 'instead of' trigger on a view for handling insert statements.", 'The inserted table contains new data in the case of an update, while the deleted table contains the old data.', "The 'inserted' table contains newly added data, while the 'deleted' table contains deleted rows.", 'The inserted table contains the ID, name, gender, and department name of the inserted row.', 'The deleted table will be empty in this scenario as no row was deleted from the view.']}, {'end': 903.853, 'segs': [{'end': 680.661, 'src': 'embed', 'start': 649.823, 'weight': 0, 'content': [{'end': 652.304, 'text': "Don't execute any further statements.", 'start': 649.823, 'duration': 2.481}, {'end': 653.785, 'text': "Don't execute these statements.", 'start': 652.464, 'duration': 1.321}, {'end': 663.699, 'text': 'So, if the department ID is valid on the other hand, then we want to continue to insert the row into tblEmployee table.', 'start': 655.633, 'duration': 8.066}, {'end': 666.941, 'text': 'So, we are saying insert into tblEmployee.', 'start': 663.719, 'duration': 3.222}, {'end': 671.965, 'text': 'Obviously, in tblEmployee, we have got ID name, gender and department ID columns.', 'start': 668.162, 'duration': 3.803}, {'end': 680.661, 'text': 'So, into these columns, insert the ID name, gender and department ID.', 'start': 672.325, 'duration': 8.336}], 'summary': 'Insert row into tblemployee if department id is valid.', 'duration': 30.838, 'max_score': 649.823, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo649823.jpg'}, {'end': 737.65, 'src': 'embed', 'start': 705.767, 'weight': 3, 'content': [{'end': 710.054, 'text': 'So we now have this trigger created on that view.', 'start': 705.767, 'duration': 4.287}, {'end': 718.899, 'text': 'so obviously, when we execute this insert into statement instead of the actual insert statement, look at this I am supplying a garbage.', 'start': 710.054, 'duration': 8.845}, {'end': 727.824, 'text': "you know this department doesn't exist, okay, so when we execute this insert query, what's gonna happen in the department table?", 'start': 718.899, 'duration': 8.925}, {'end': 737.65, 'text': "if you look at department table, you only have IT, payroll, HR and admin, but in the query you're inserting a garbage.", 'start': 727.824, 'duration': 9.826}], 'summary': 'Trigger created on view; inserting non-existent department into department table.', 'duration': 31.883, 'max_score': 705.767, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo705767.jpg'}, {'end': 903.853, 'src': 'embed', 'start': 897.065, 'weight': 1, 'content': [{'end': 901.07, 'text': 'On this slide, you can find resources for ASP.NET C-Sharp and SQL Server interview questions.', 'start': 897.065, 'duration': 4.005}, {'end': 901.871, 'text': "That's it for today.", 'start': 901.13, 'duration': 0.741}, {'end': 902.912, 'text': 'Thank you for listening.', 'start': 902.171, 'duration': 0.741}, {'end': 903.853, 'text': 'Have a great day.', 'start': 903.212, 'duration': 0.641}], 'summary': 'Resources for asp.net c-sharp and sql server interview questions provided. end of session.', 'duration': 6.788, 'max_score': 897.065, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo897065.jpg'}], 'start': 573.503, 'title': 'Sql server triggers', 'summary': "Discusses the implementation of sql server triggers for error handling and instead of insert trigger, covering the use of raiseerror function, 'inserted' and 'deleted' tables, and foreign key violations.", 'chapters': [{'end': 793.922, 'start': 573.503, 'title': 'Sql server trigger - error handling', 'summary': 'Discusses the implementation of a trigger in sql server to handle errors when inserting data into a table, using a raiseerror function to throw an error if the department id is null, preventing the insertion of invalid data and maintaining data integrity.', 'duration': 220.419, 'highlights': ['Using raiseError function to throw an error if the department ID is null', 'Severity level and state parameters for raiseError function', 'Preventing further processing with the return keyword', 'Inserting valid data into the tblEmployee table']}, {'end': 903.853, 'start': 794.842, 'title': 'Understanding instead of insert trigger', 'summary': "Explains the implementation of an instead of insert trigger in sql, demonstrating the insertion of a record into the tbl employee table with the corresponding department id, utilization of special tables 'inserted' and 'deleted' by triggers, and handling foreign key violations in the context of tbl employee and tbl department tables.", 'duration': 109.011, 'highlights': ["The chapter explains the implementation of an instead of insert trigger in SQL, demonstrating the insertion of a record into the TBL employee table with the corresponding department ID, utilization of special tables 'inserted' and 'deleted' by triggers, and handling foreign key violations in the context of TBL employee and TBL department tables.", "Valerie's record is inserted into TBL employee table with department ID equal to 1, and the view also reflects the new row with the department name 'IT'.", "The trigger uses the special tables 'inserted' and 'deleted' to obtain department IDs and requires a good understanding of joins to retrieve the department ID by joining the inserted table with TBL department.", 'In case of garbage input for the department name in the insert query, which does not exist in TBL department table, an error is raised to prevent foreign key violations between TBL employee and TBL department tables.']}], 'duration': 330.35, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/MseKoztMpoo/pics/MseKoztMpoo573503.jpg', 'highlights': ["The chapter explains the implementation of an instead of insert trigger in SQL, demonstrating the insertion of a record into the TBL employee table with the corresponding department ID, utilization of special tables 'inserted' and 'deleted' by triggers, and handling foreign key violations in the context of TBL employee and TBL department tables.", 'Using raiseError function to throw an error if the department ID is null', 'In case of garbage input for the department name in the insert query, which does not exist in TBL department table, an error is raised to prevent foreign key violations between TBL employee and TBL department tables.', "Valerie's record is inserted into TBL employee table with department ID equal to 1, and the view also reflects the new row with the department name 'IT'."]}], 'highlights': ['Creation of a view based on multiple tables and encountering the error of modification affecting multiple base tables', 'The trigger retrieves the department ID from TBL department table and inserts it along with ID, name, and gender into TBL employee table', 'Demonstration of creating an instead of insert trigger to correctly insert data into the underlying base table', 'Using raiseError function to throw an error if the department ID is null', 'Handling foreign key violations in the context of TBL employee and TBL department tables']}