title
Instead of update triggers in sql server - Part 46.avi

description
In this video we will learn about instead of update trigger. instead of update trigger, can be used to update views that are based on multiple base tables. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/instead-of-update-triggers-part-46.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-46-instead-of-update-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 update triggers in sql server - Part 46.avi', 'heatmap': [{'end': 442.102, 'start': 411.821, 'weight': 0.731}, {'end': 633.696, 'start': 614.02, 'weight': 0.759}, {'end': 776.045, 'start': 747.159, 'weight': 0.728}, {'end': 902.141, 'start': 848.775, 'weight': 1}], 'summary': 'Series explores instead of update triggers in sql server, covering three types of triggers, classification of dml triggers, challenges of updating views affecting multiple base tables, usage of inserted and deleted tables, and practical examples for handling updates and maintaining data integrity.', 'chapters': [{'end': 183.922, 'segs': [{'end': 65.738, 'src': 'embed', 'start': 0.049, 'weight': 0, 'content': [{'end': 1.99, 'text': 'Hello, welcome to Prejim Technologies.', 'start': 0.049, 'duration': 1.941}, {'end': 2.851, 'text': 'I am Venkat.', 'start': 2.291, 'duration': 0.56}, {'end': 5.153, 'text': 'This is part 46 of SQL Server.', 'start': 3.111, 'duration': 2.042}, {'end': 9.036, 'text': "In this session, we'll learn about instead of update trigger.", 'start': 5.953, 'duration': 3.083}, {'end': 15.341, 'text': 'Before continuing with this session, I strongly recommend to watch parts 43, 44, and 45 of this video series.', 'start': 9.436, 'duration': 5.905}, {'end': 20.244, 'text': 'So in SQL Server, there are three types of triggers, DML, DDL, and logon.', 'start': 16.001, 'duration': 4.243}, {'end': 24.127, 'text': 'DML triggers are fired automatically in response to DML events.', 'start': 20.845, 'duration': 3.282}, {'end': 27.71, 'text': 'Examples of DML events include insert, update, and delete.', 'start': 24.548, 'duration': 3.162}, {'end': 33.001, 'text': 'DML triggers can be further classified into two types, after triggers and instead of triggers.', 'start': 28.317, 'duration': 4.684}, {'end': 39.768, 'text': 'After triggers are fired after the triggering action, whereas instead of triggers are fired instead of the triggering action.', 'start': 33.442, 'duration': 6.326}, {'end': 49.707, 'text': "We have spoken about instead of insert trigger in the previous session, in this session we'll learn about instead of update trigger.", 'start': 42.802, 'duration': 6.905}, {'end': 51.788, 'text': "Let's understand that with an example.", 'start': 50.047, 'duration': 1.741}, {'end': 56.692, 'text': 'We have two tables here, TBL department table and TBL employee table.', 'start': 52.249, 'duration': 4.443}, {'end': 60.134, 'text': 'Now this view is based on these two tables.', 'start': 57.112, 'duration': 3.022}, {'end': 65.738, 'text': 'So if you look at this view, the ID name and gender columns are coming from TBL employee table.', 'start': 60.554, 'duration': 5.184}], 'summary': 'Sql server session on instead of update trigger in part 46.', 'duration': 65.689, 'max_score': 0.049, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc49.jpg'}, {'end': 177.06, 'src': 'embed', 'start': 146.271, 'weight': 4, 'content': [{'end': 149.673, 'text': 'so we have these two tables TBL employee and TBL department.', 'start': 146.271, 'duration': 3.402}, {'end': 151.311, 'text': "Let's create a view on them.", 'start': 150.131, 'duration': 1.18}, {'end': 160.354, 'text': 'So create view view name as select the required columns ID name, gender, department name from TBL employee, joining that with TBL department.', 'start': 151.351, 'duration': 9.003}, {'end': 166.576, 'text': 'you know on the department ID column which is common between TBL employee and TBL department.', 'start': 160.354, 'duration': 6.222}, {'end': 172.538, 'text': 'We have learned about views, you know, and joins in the previous sessions.', 'start': 166.856, 'duration': 5.682}, {'end': 177.06, 'text': 'So please check those sessions if you are new to views and joins.', 'start': 172.898, 'duration': 4.162}], 'summary': 'Creating a view that selects specific columns from tbl employee and tbl department, joined on department id.', 'duration': 30.789, 'max_score': 146.271, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc146271.jpg'}], 'start': 0.049, 'title': 'Instead of update trigger', 'summary': 'Introduces instead of update triggers in sql server, explaining the three types of triggers and the classification of dml triggers, and discusses the concept with an example illustrating its use in handling updates affecting multiple base tables in a view.', 'chapters': [{'end': 39.768, 'start': 0.049, 'title': 'Sql server: instead of update trigger', 'summary': 'Introduces instead of update triggers in sql server, explaining the three types of triggers (dml, ddl, and logon) and the classification of dml triggers into after and instead of triggers.', 'duration': 39.719, 'highlights': ['DML triggers in SQL Server are classified into three types: DML, DDL, and logon.', 'It is recommended to watch parts 43, 44, and 45 of the video series before proceeding with this session.', 'DML triggers can be further classified into after triggers and instead of triggers.', 'Examples of DML events that can trigger DML triggers include insert, update, and delete.']}, {'end': 183.922, 'start': 42.802, 'title': 'Instead of update trigger', 'summary': 'Discusses the concept of instead of update trigger, with an example illustrating how an update affecting multiple base tables in a view can be handled using this trigger, preventing errors and ensuring data integrity.', 'duration': 141.12, 'highlights': ['The concept of instead of update trigger is explained, with an example demonstrating how an update affecting multiple base tables in a view can be handled (e.g., updating name and department name columns) to prevent errors and ensure data integrity.', 'Creation of a view based on multiple base tables, TBL department, and TBL employee, is demonstrated, with an example illustrating the use of join on a common column (department ID) to create the view.']}], 'duration': 183.873, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc49.jpg', 'highlights': ['DML triggers in SQL Server are classified into three types: DML, DDL, and logon.', 'DML triggers can be further classified into after triggers and instead of triggers.', 'Examples of DML events that can trigger DML triggers include insert, update, and delete.', 'The concept of instead of update trigger is explained, with an example demonstrating how an update affecting multiple base tables in a view can be handled (e.g., updating name and department name columns) to prevent errors and ensure data integrity.', 'Creation of a view based on multiple base tables, TBL department, and TBL employee, is demonstrated, with an example illustrating the use of join on a common column (department ID) to create the view.', 'It is recommended to watch parts 43, 44, and 45 of the video series before proceeding with this session.']}, {'end': 522.614, 'segs': [{'end': 275.885, 'src': 'embed', 'start': 231.512, 'weight': 0, 'content': [{'end': 238.876, 'text': 'now, to overcome this, we can actually create an instead of update trigger, because this is an update statement.', 'start': 231.512, 'duration': 7.364}, {'end': 243.499, 'text': 'so on this view, we can create an instead of update trigger and and it works.', 'start': 238.876, 'duration': 4.623}, {'end': 246.381, 'text': "but then, before we do that, let's look at another example.", 'start': 243.499, 'duration': 2.882}, {'end': 254.191, 'text': 'so we have exactly the same two tables, tblDepartment and tblEmployee and the same view.', 'start': 248.507, 'duration': 5.684}, {'end': 257.072, 'text': 'now the only difference here is the update statement.', 'start': 254.191, 'duration': 2.881}, {'end': 265.158, 'text': 'so if you look at the update statement, here we are updating the view and we are only changing one column, that is, department name column,', 'start': 257.072, 'duration': 8.086}, {'end': 268.26, 'text': 'and we know that department name column is coming from tblDepartment.', 'start': 265.158, 'duration': 3.102}, {'end': 271.942, 'text': 'so this update statement is affecting only one base table.', 'start': 268.26, 'duration': 3.682}, {'end': 274.084, 'text': "it's not affecting multiple base tables.", 'start': 271.942, 'duration': 2.142}, {'end': 275.885, 'text': 'so it should succeed.', 'start': 274.084, 'duration': 1.801}], 'summary': "Creating an 'instead of update' trigger can overcome update issues on views with one base table.", 'duration': 44.373, 'max_score': 231.512, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc231512.jpg'}, {'end': 442.102, 'src': 'heatmap', 'start': 361.383, 'weight': 2, 'content': [{'end': 366.846, 'text': 'So, if you update a view that is based on multiple tables, there are two things that can happen.', 'start': 361.383, 'duration': 5.463}, {'end': 373.17, 'text': 'If your update statement affects multiple base tables, then an error will be thrown and the statement will be terminated.', 'start': 367.366, 'duration': 5.804}, {'end': 384.856, 'text': 'But whereas if your update works I mean if your update only affects one table, you know, if you just update the name and gender it may work correctly.', 'start': 373.75, 'duration': 11.106}, {'end': 394.141, 'text': 'But if you update the department name, then it may incorrectly update, you know, the update might happen, you know, in a wrong way.', 'start': 385.176, 'duration': 8.965}, {'end': 398.673, 'text': 'And to overcome that, we can make use of instead of update triggers.', 'start': 394.71, 'duration': 3.963}, {'end': 404.156, 'text': "So let's now see how to create an instead of update trigger to correct the situation.", 'start': 398.973, 'duration': 5.183}, {'end': 420.067, 'text': "OK So let's try to execute this query and see if it actually wrongly updates.", 'start': 411.821, 'duration': 8.246}, {'end': 425.053, 'text': "So when we select the data now, it's showing correctly John belongs to HR.", 'start': 420.529, 'duration': 4.524}, {'end': 427.336, 'text': 'Ben also belongs to HR department.', 'start': 425.474, 'duration': 1.862}, {'end': 430.699, 'text': "Now let's execute this update statement.", 'start': 427.676, 'duration': 3.023}, {'end': 433.001, 'text': 'So we are updating employee details.', 'start': 431.139, 'duration': 1.862}, {'end': 442.102, 'text': "We are asking to change the department name to IT for record with ID is equal to 1, meaning change John's department name to IT.", 'start': 433.341, 'duration': 8.761}], 'summary': 'Updating a view based on multiple tables can cause errors and incorrect updates; using instead of update triggers can correct the situation.', 'duration': 58.684, 'max_score': 361.383, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc361383.jpg'}, {'end': 522.614, 'src': 'embed', 'start': 494.947, 'weight': 5, 'content': [{'end': 500.709, 'text': 'So update TBL department set department name is equal to HR, where department ID is 3.', 'start': 494.947, 'duration': 5.762}, {'end': 502.05, 'text': "So let's execute this query.", 'start': 500.709, 'duration': 1.341}, {'end': 508.693, 'text': "And let's select the data from the tables just to make sure it's as expected.", 'start': 503.631, 'duration': 5.062}, {'end': 512.494, 'text': 'So everything is correct now, IT payroll HR admin.', 'start': 509.533, 'duration': 2.961}, {'end': 514.269, 'text': 'Okay, cool.', 'start': 513.227, 'duration': 1.042}, {'end': 516.289, 'text': "Now let's go ahead and create that view.", 'start': 514.589, 'duration': 1.7}, {'end': 522.614, 'text': "Now this view, if you look at it, it's very big, but then it's very simple to understand.", 'start': 516.91, 'duration': 5.704}], 'summary': 'Updated department name to hr with id 3. verified data and created a new view.', 'duration': 27.667, 'max_score': 494.947, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc494947.jpg'}], 'start': 184.383, 'title': 'Updating views and instead of update triggers', 'summary': 'Discusses challenges of updating views affecting multiple base tables and solutions using instead of update triggers, with specific examples and demonstrations of successful and incorrect updates.', 'chapters': [{'end': 360.983, 'start': 184.383, 'title': 'View update statements and base tables', 'summary': 'Discusses the challenges with updating views that affect multiple base tables and the solution of using instead of update triggers, along with an example of a successful update statement affecting only one base table.', 'duration': 176.6, 'highlights': ['Creating an instead of update trigger can overcome the issue of updating views that affect multiple base tables, ensuring successful execution of update statements.', 'The update statement affecting only one base table successfully updates the specified column in the corresponding base table, without impacting other related data.', 'The unintended consequence of updating a view can result in incorrect data representation and the need to avoid such situations for accurate data management.']}, {'end': 522.614, 'start': 361.383, 'title': 'Updating views and using instead of update triggers', 'summary': 'Discusses the challenges of updating views based on multiple tables and demonstrates the use of instead of update triggers to prevent incorrect updates, with a specific example of updating department names leading to unexpected results.', 'duration': 161.231, 'highlights': ['Creating an instead of update trigger to prevent incorrect updates, demonstrated through an example of updating department names leading to unexpected results.', 'Illustrating the consequences of updating a view based on multiple tables, where an update statement affecting multiple base tables may throw an error and be terminated, while an update affecting only one table, such as updating name and gender, may work correctly.', 'Reverting the department name back to HR using an update query and confirming the correctness of the data by selecting the tables.']}], 'duration': 338.231, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc184383.jpg', 'highlights': ['Creating an instead of update trigger can overcome the issue of updating views that affect multiple base tables, ensuring successful execution of update statements.', 'The update statement affecting only one base table successfully updates the specified column in the corresponding base table, without impacting other related data.', 'Creating an instead of update trigger to prevent incorrect updates, demonstrated through an example of updating department names leading to unexpected results.', 'The unintended consequence of updating a view can result in incorrect data representation and the need to avoid such situations for accurate data management.', 'Illustrating the consequences of updating a view based on multiple tables, where an update statement affecting multiple base tables may throw an error and be terminated, while an update affecting only one table, such as updating name and gender, may work correctly.', 'Reverting the department name back to HR using an update query and confirming the correctness of the data by selecting the tables.']}, {'end': 856.545, 'segs': [{'end': 548.685, 'src': 'embed', 'start': 522.654, 'weight': 0, 'content': [{'end': 529.399, 'text': "It's just that a bit of copy pasted code, if you understand one section, is pretty much similar.", 'start': 522.654, 'duration': 6.745}, {'end': 537.917, 'text': 'Okay, so we know that triggers makes use of two special tables called inserted and deleted, and we have spoken about them, you know,', 'start': 529.992, 'duration': 7.925}, {'end': 540.099, 'text': 'in the previous three videos parts.', 'start': 537.917, 'duration': 2.182}, {'end': 548.685, 'text': 'Okay, so inserted table contains the new data that you have inserted, whereas deleted table will contain the rows that you have deleted.', 'start': 540.579, 'duration': 8.106}], 'summary': 'Triggers use inserted and deleted tables to track new and deleted data.', 'duration': 26.031, 'max_score': 522.654, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc522654.jpg'}, {'end': 640.99, 'src': 'heatmap', 'start': 614.02, 'weight': 0.759, 'content': [{'end': 622.947, 'text': 'so when somebody says update tbl department name, then update of id.', 'start': 614.02, 'duration': 8.927}, {'end': 629.372, 'text': 'now this update statement is actually updating department name column, not id column.', 'start': 622.947, 'duration': 6.425}, {'end': 633.696, 'text': "So this function will return false and we wouldn't get into this block.", 'start': 629.653, 'duration': 4.043}, {'end': 640.99, 'text': "But whereas if you look at this condition, it's actually checking update department name.", 'start': 634.668, 'duration': 6.322}], 'summary': 'The update statement is checking department name, returning false and not entering the block.', 'duration': 26.97, 'max_score': 614.02, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc614020.jpg'}, {'end': 669.401, 'src': 'embed', 'start': 643.231, 'weight': 4, 'content': [{'end': 649.953, 'text': 'So basically you can use this update function to determine if the user is actually updating that column.', 'start': 643.231, 'duration': 6.722}, {'end': 656.495, 'text': 'So if you look at the view, the view has got id, name, gender and department name columns.', 'start': 650.473, 'duration': 6.022}, {'end': 660.096, 'text': 'So we have to check if each column has been changed.', 'start': 656.795, 'duration': 3.301}, {'end': 664.02, 'text': 'And to do that, you can actually make use of this update function.', 'start': 660.859, 'duration': 3.161}, {'end': 669.401, 'text': 'So this function checks if the column has been changed by the update statement.', 'start': 664.32, 'duration': 5.081}], 'summary': 'The update function checks if user is updating columns in the view.', 'duration': 26.17, 'max_score': 643.231, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc643231.jpg'}, {'end': 721.31, 'src': 'embed', 'start': 695.793, 'weight': 3, 'content': [{'end': 700.497, 'text': 'If you want to throw a custom error, then you can make use of raiseError function.', 'start': 695.793, 'duration': 4.704}, {'end': 704.441, 'text': 'So we are throwing an error message saying ID cannot be changed.', 'start': 701.138, 'duration': 3.303}, {'end': 706.962, 'text': 'And this is severity level and state.', 'start': 704.801, 'duration': 2.161}, {'end': 713.045, 'text': "We'll talk about raise error and exception handling in a great detail in a later session when we talk about exception handling.", 'start': 707.222, 'duration': 5.823}, {'end': 719.769, 'text': 'So when somebody is trying to change ID, we want to raise this error and return from here.', 'start': 713.486, 'duration': 6.283}, {'end': 721.31, 'text': "We don't want to process anymore.", 'start': 719.809, 'duration': 1.501}], 'summary': 'Using raiseerror function to throw custom error message with severity level and state, when trying to change id.', 'duration': 25.517, 'max_score': 695.793, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc695793.jpg'}, {'end': 776.045, 'src': 'heatmap', 'start': 733.973, 'weight': 2, 'content': [{'end': 745.638, 'text': 'so if somebody is issuing a query like this, set the department name to HR, meaning we are intending to change the department name.', 'start': 733.973, 'duration': 11.665}, {'end': 754.863, 'text': 'So, if the department name is changed, then our intention is basically to update the department ID column.', 'start': 747.159, 'duration': 7.704}, {'end': 759.66, 'text': 'not to change the department name column in TBL department table.', 'start': 755.876, 'duration': 3.784}, {'end': 760.5, 'text': 'so to do that,', 'start': 759.66, 'duration': 0.84}, {'end': 776.045, 'text': 'obviously you will have to get the department ID that is associated with this department name and to do that you can actually join the newly entered department name with tblDepartmentTable.', 'start': 760.5, 'duration': 15.545}], 'summary': 'Query intends to update department name to hr, and subsequently update department id column in tbl department table.', 'duration': 26.527, 'max_score': 733.973, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc733973.jpg'}, {'end': 863.634, 'src': 'embed', 'start': 837.721, 'weight': 1, 'content': [{'end': 843.946, 'text': 'So obviously, if I type in a garbage like this, obviously, there is no entry, there is no department with that name.', 'start': 837.721, 'duration': 6.225}, {'end': 846.488, 'text': "So I don't get any department ID back.", 'start': 844.227, 'duration': 2.261}, {'end': 848.41, 'text': 'So this will be null.', 'start': 847.329, 'duration': 1.081}, {'end': 854.582, 'text': 'If that is null, then we want to throw an error saying that invalid department name and then return back.', 'start': 848.775, 'duration': 5.807}, {'end': 856.545, 'text': "You don't want to process anymore.", 'start': 855.103, 'duration': 1.442}, {'end': 863.634, 'text': 'Otherwise, if you get the valid department ID back, then what you do? You update the tblEmployee table.', 'start': 858.447, 'duration': 5.187}], 'summary': 'Invalid department name results in null department id and triggers an error; valid department id leads to updating tblemployee table.', 'duration': 25.913, 'max_score': 837.721, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc837721.jpg'}], 'start': 522.654, 'title': 'Sql triggers and update function in sql', 'summary': 'Covers the usage of inserted and deleted tables in sql triggers, enabling error handling for prohibited updates, and handling department name changes to ensure data integrity and prevent processing of invalid department names.', 'chapters': [{'end': 563.434, 'start': 522.654, 'title': 'Sql triggers and special tables', 'summary': 'Covers the usage of inserted and deleted tables in sql triggers, which contain new and old data, and are utilized in update operations.', 'duration': 40.78, 'highlights': ['The inserted table contains the new data that has been inserted, while the deleted table contains the rows that have been deleted.', 'When updating a view or table, the inserted table will contain the updated new data, while the deleted table will contain the old data before the update.', 'Triggers make use of two special tables called inserted and deleted, which are essential for handling insert, update, and delete operations in SQL.']}, {'end': 713.045, 'start': 563.834, 'title': 'Using triggers and update function in sql', 'summary': 'Discusses creating a trigger for a view to use the update function to determine if a specific column has been changed, enabling error handling for prohibited updates, and mentions the upcoming coverage of raiseerror and exception handling in a later session.', 'duration': 149.211, 'highlights': ['Creating a trigger for a view to use the update function to determine if a specific column has been changed.', 'Enabling error handling for prohibited updates using the raiseError function.', 'Mentioning the upcoming coverage of raiseError and exception handling in a later session.']}, {'end': 856.545, 'start': 713.486, 'title': 'Handling department name change', 'summary': 'Details the process of handling department name changes, including the use of the return keyword to raise errors, checking for changes in department name, and validating department id, to ensure data integrity and prevent processing of invalid department names.', 'duration': 143.059, 'highlights': ['The use of the return keyword to raise errors and interrupt processing when attempting to change an ID.', 'Checking for department name changes and updating the associated department ID column to maintain data integrity.', 'Validating department ID to prevent processing of invalid department names, ensuring data accuracy and reliability.']}], 'duration': 333.891, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc522654.jpg', 'highlights': ['Triggers make use of two special tables called inserted and deleted, essential for handling insert, update, and delete operations in SQL.', 'Validating department ID to prevent processing of invalid department names, ensuring data accuracy and reliability.', 'Checking for department name changes and updating the associated department ID column to maintain data integrity.', 'Enabling error handling for prohibited updates using the raiseError function.', 'Creating a trigger for a view to use the update function to determine if a specific column has been changed.']}, {'end': 1345.889, 'segs': [{'end': 908.604, 'src': 'embed', 'start': 858.447, 'weight': 1, 'content': [{'end': 863.634, 'text': 'Otherwise, if you get the valid department ID back, then what you do? You update the tblEmployee table.', 'start': 858.447, 'duration': 5.187}, {'end': 868.284, 'text': 'set department ID is equal to add department ID.', 'start': 865.362, 'duration': 2.922}, {'end': 875.228, 'text': "obviously you'll have to join that with inserted table, because in the inserted table you have the employee ID.", 'start': 868.284, 'duration': 6.944}, {'end': 878.89, 'text': 'so you want to be updating that correct employee.', 'start': 875.228, 'duration': 3.662}, {'end': 879.57, 'text': "that's it.", 'start': 878.89, 'duration': 0.68}, {'end': 883.552, 'text': "so if department name is changed, that's how you update that.", 'start': 879.57, 'duration': 3.982}, {'end': 888.915, 'text': "on the other hand, if they're changing the gender, it's going to be straightforward.", 'start': 883.552, 'duration': 5.363}, {'end': 902.141, 'text': 'all you have to do is join inserted table with TBL employee and use the gender column in inserted table to set that as the value for the gender column in tbl employees table,', 'start': 888.915, 'duration': 13.226}, {'end': 905.963, 'text': 'which is again very straightforward and along the same lines for the name.', 'start': 902.141, 'duration': 3.822}, {'end': 908.604, 'text': 'use the name column from the inserted table.', 'start': 905.963, 'duration': 2.641}], 'summary': 'Updating tblemployee table with department id, gender, and name from inserted table.', 'duration': 50.157, 'max_score': 858.447, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc858447.jpg'}, {'end': 951.032, 'src': 'embed', 'start': 927.608, 'weight': 3, 'content': [{'end': 935.474, 'text': "then you'll have to get the department ID from tblDepartmentTable by joining the inserted table with tblDepartmentTable.", 'start': 927.608, 'duration': 7.866}, {'end': 945.06, 'text': "And obviously, there are chances within the update statement users can type in a department name that doesn't exist in tblDepartmentTable.", 'start': 936.334, 'duration': 8.726}, {'end': 951.032, 'text': "If that's the case, you have to throw an error saying that invalid department name.", 'start': 946.181, 'duration': 4.851}], 'summary': 'Join tbldepartmenttable to get department id, handle invalid department names.', 'duration': 23.424, 'max_score': 927.608, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc927608.jpg'}, {'end': 1141.377, 'src': 'embed', 'start': 1103.07, 'weight': 0, 'content': [{'end': 1108.414, 'text': "alright, now let's try to change more than one column.", 'start': 1103.07, 'duration': 5.344}, {'end': 1112.954, 'text': "okay, so here I'm trying to.", 'start': 1108.414, 'duration': 4.54}, {'end': 1117.861, 'text': "let's select the data from the view.", 'start': 1112.954, 'duration': 4.907}, {'end': 1124.971, 'text': "so let's try to change name, department name, and let's also try to change his gender.", 'start': 1117.861, 'duration': 7.11}, {'end': 1131.591, 'text': 'okay, so we have this query here.', 'start': 1129.409, 'duration': 2.182}, {'end': 1134.493, 'text': "so we're trying to update this view.", 'start': 1131.591, 'duration': 2.902}, {'end': 1141.377, 'text': "change the name from John to Johnny, gender from male to female and department name from IT to, let's say, payroll.", 'start': 1134.493, 'duration': 6.884}], 'summary': 'Updating multiple columns in a data view: name from john to johnny, gender from male to female, and department from it to payroll.', 'duration': 38.307, 'max_score': 1103.07, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc1103070.jpg'}, {'end': 1273.816, 'src': 'embed', 'start': 1249.69, 'weight': 4, 'content': [{'end': 1256.913, 'text': "but then when you update this query, you are actually trying to change the name here, but it's the same value.", 'start': 1249.69, 'duration': 7.223}, {'end': 1266.057, 'text': 'but in your trigger this function update of name will actually returns true, even if you are not.', 'start': 1256.913, 'duration': 9.144}, {'end': 1273.816, 'text': "I mean you're not actually changing to a different value, changing it to the same value, but still the update function will return true.", 'start': 1266.057, 'duration': 7.759}], 'summary': 'Updating query returns true for unchanged value.', 'duration': 24.126, 'max_score': 1249.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc1249690.jpg'}], 'start': 858.447, 'title': 'Using triggers and updating views', 'summary': 'Explains the use of triggers to update employee information based on changes in specified columns, and discusses updating views, using update functions, and handling triggers in sql server with practical examples.', 'chapters': [{'end': 974.417, 'start': 858.447, 'title': 'Using triggers to update employee information', 'summary': 'Explains how to use triggers to update employee information in the tblemployee table based on changes in department, gender, or name columns from the inserted table, while ensuring data integrity and error handling.', 'duration': 115.97, 'highlights': ['Updating the department ID in tblEmployee table based on changes in department name involves joining the inserted table with tblDepartmentTable to validate the department ID and ensure data integrity.', 'Updating the gender and name columns in tblEmployee table based on changes in the inserted table is straightforward and involves joining the inserted table with tblEmployee on the ID column.', 'The update statement involves joins and requires understanding of joins to ensure accurate updates and error handling for invalid department names.', 'If the user tries to update the ID column, an error should be thrown to maintain data integrity.', 'If the department name specified by the user in the update statement does not exist in tblDepartmentTable, an error should be thrown to handle invalid department names.']}, {'end': 1345.889, 'start': 980.848, 'title': 'Updating views and using update function', 'summary': 'Discusses updating views, using update function, and handling triggers in sql server, showcasing examples of updating a view, changing multiple columns, and the behavior of the update function.', 'duration': 365.041, 'highlights': ['The view is updated successfully to change multiple columns - name from John to Johnny, gender from male to female, and department name from IT to payroll, demonstrating that the view is updatable when the modification affects multiple base tables.', 'The update function in the trigger returns true even when setting a column to the same value, highlighting the need to manually compare column values between inserted and deleted tables for accurate data validation.', 'The chapter concludes with a note on resources for ASP.NET C Sharp and SQL Server interview questions, providing additional learning opportunities for the audience.']}], 'duration': 487.442, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pkQk1Z834Rc/pics/pkQk1Z834Rc858447.jpg', 'highlights': ['The view is updated successfully to change multiple columns - name from John to Johnny, gender from male to female, and department name from IT to payroll, demonstrating that the view is updatable when the modification affects multiple base tables.', 'Updating the department ID in tblEmployee table based on changes in department name involves joining the inserted table with tblDepartmentTable to validate the department ID and ensure data integrity.', 'Updating the gender and name columns in tblEmployee table based on changes in the inserted table is straightforward and involves joining the inserted table with tblEmployee on the ID column.', 'The update statement involves joins and requires understanding of joins to ensure accurate updates and error handling for invalid department names.', 'The update function in the trigger returns true even when setting a column to the same value, highlighting the need to manually compare column values between inserted and deleted tables for accurate data validation.']}], 'highlights': ['DML triggers in SQL Server are classified into three types: DML, DDL, and logon.', 'Triggers make use of two special tables called inserted and deleted, essential for handling insert, update, and delete operations in SQL.', 'Creating an instead of update trigger can overcome the issue of updating views that affect multiple base tables, ensuring successful execution of update statements.', 'The view is updated successfully to change multiple columns - name from John to Johnny, gender from male to female, and department name from IT to payroll, demonstrating that the view is updatable when the modification affects multiple base tables.', 'Examples of DML events that can trigger DML triggers include insert, update, and delete.']}