title
DML triggers in sql server Part 43

description
In this video we will learn about triggers in sql serevr. There are 3 types of triggers 1. DML triggers 2. DDL triggers 3. Logon trigger 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 DML triggers are fired automatically in response to DML events (INSERT, UPDATE & DELETE). DML triggers can be again classified into 2 types 1. After triggers (Sometimes called as FOR triggers) 2. Instead of triggers. After triggers, fires after the triggering action. The INSERT, UPDATE, and DELETE statements, causes an after trigger to fire after the respective statements complete execution. INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, causes an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/dml-triggers-part-43.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-43-dml-triggers.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'DML triggers in sql server Part 43', 'heatmap': [{'end': 438.898, 'start': 413.524, 'weight': 1}, {'end': 543.727, 'start': 464.8, 'weight': 0.792}], 'summary': 'Covers sql server dml triggers, including after and instead of triggers, and the implementation of automatic audit logging for the tblemployee table, capturing record ids, insertion timestamps, and new employee additions and deletions audit information.', 'chapters': [{'end': 172.993, 'segs': [{'end': 37.795, 'src': 'embed', 'start': 0.009, 'weight': 1, 'content': [{'end': 1.85, 'text': 'Hello, welcome to Prezim Technologies.', 'start': 0.009, 'duration': 1.841}, {'end': 2.731, 'text': 'I am Venkat.', 'start': 2.15, 'duration': 0.581}, {'end': 4.972, 'text': 'This is part 43 of SQL Server.', 'start': 2.971, 'duration': 2.001}, {'end': 9.996, 'text': "In this session, we'll learn about the different types of triggers that are available in SQL Server.", 'start': 5.533, 'duration': 4.463}, {'end': 16.68, 'text': 'In SQL Server, there are three types of triggers, DML, DDL, and logon triggers.', 'start': 10.997, 'duration': 5.683}, {'end': 20.063, 'text': "In this session, we'll talk about DML triggers.", 'start': 17.421, 'duration': 2.642}, {'end': 24.486, 'text': 'DDL and logon triggers, we will talk about them in a later session.', 'start': 20.643, 'duration': 3.843}, {'end': 27.608, 'text': 'DML stands for Data Manipulation Language.', 'start': 25.046, 'duration': 2.562}, {'end': 37.795, 'text': "Insert, update, and delete statements are examples for DML statements because they modify the data that's present inside the table or a view.", 'start': 28.211, 'duration': 9.584}], 'summary': 'Session covers dml triggers in sql server, part 43.', 'duration': 37.786, 'max_score': 0.009, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w9.jpg'}, {'end': 99.128, 'src': 'embed', 'start': 54.962, 'weight': 0, 'content': [{'end': 57.243, 'text': 'these triggers are called as DML triggers.', 'start': 54.962, 'duration': 2.281}, {'end': 64.778, 'text': 'Now, DML triggers can again be classified into two types, after triggers instead of triggers.', 'start': 58.453, 'duration': 6.325}, {'end': 68.681, 'text': "After triggers, sometimes they're also called as for triggers.", 'start': 65.298, 'duration': 3.383}, {'end': 73.024, 'text': 'So for or after triggers in SQL Server means one and the same.', 'start': 69.041, 'duration': 3.983}, {'end': 77.427, 'text': "Now, let's understand the difference between after triggers and instead of triggers.", 'start': 73.344, 'duration': 4.083}, {'end': 82.91, 'text': 'After triggers, as the name says, they fire after the triggering action.', 'start': 78.187, 'duration': 4.723}, {'end': 86.533, 'text': 'And the triggering action could be insert, update, and delete.', 'start': 83.291, 'duration': 3.242}, {'end': 92.863, 'text': 'So after triggers, they fire after an insert, update, or delete statement execution.', 'start': 86.857, 'duration': 6.006}, {'end': 99.128, 'text': 'Whereas instead of triggers, as the name says, they fire instead of the triggering action.', 'start': 93.543, 'duration': 5.585}], 'summary': 'Dml triggers in sql server include after and instead of triggers, which differ in their firing times and triggering actions.', 'duration': 44.166, 'max_score': 54.962, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w54962.jpg'}], 'start': 0.009, 'title': 'Sql server dml triggers', 'summary': 'Covers the different types of triggers in sql server, focusing on dml triggers, which are classified into after triggers and instead of triggers, and their respective functionalities. it also mentions the three types of triggers in sql server - dml, ddl, and logon triggers.', 'chapters': [{'end': 172.993, 'start': 0.009, 'title': 'Sql server dml triggers', 'summary': 'Covers the different types of triggers in sql server, focusing on dml triggers, which are classified into after triggers and instead of triggers, and their respective functionalities. there are three types of triggers in sql server - dml, ddl, and logon triggers.', 'duration': 172.984, 'highlights': ['There are three types of triggers in SQL Server - DML, DDL, and logon triggers.', 'DML triggers are classified into after triggers and instead of triggers.', 'After triggers fire after the triggering action of insert, update, or delete, while instead of triggers fire instead of the triggering action.']}], 'duration': 172.984, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w9.jpg', 'highlights': ['DML triggers are classified into after triggers and instead of triggers.', 'There are three types of triggers in SQL Server - DML, DDL, and logon triggers.', 'After triggers fire after the triggering action of insert, update, or delete.']}, {'end': 774.626, 'segs': [{'end': 224.605, 'src': 'embed', 'start': 174.974, 'weight': 0, 'content': [{'end': 178.076, 'text': 'Alright, so we have this table tblEmployee with these columns.', 'start': 174.974, 'duration': 3.102}, {'end': 186.627, 'text': "Now let's say whenever somebody inserts a row into this table, that could be directly using a SQL statement or through an application.", 'start': 178.477, 'duration': 8.15}, {'end': 191.15, 'text': 'So, whenever somebody adds a new employee into this TBL employee table,', 'start': 186.647, 'duration': 4.503}, {'end': 197.914, 'text': 'I want some audit information to be captured automatically in this TBL audit table that you can see on the right hand side.', 'start': 191.15, 'duration': 6.764}, {'end': 201.997, 'text': 'This audit table has got ID and audit data columns.', 'start': 198.234, 'duration': 3.763}, {'end': 210.362, 'text': "Now let's say for example, if I insert a new employee with ID is equal to 8 and let's say whatever his name, salary, etc.", 'start': 202.077, 'duration': 8.285}, {'end': 217.756, 'text': 'So whenever I insert a new row into this TBL employee table, Now, in the audit table, I want a message like this.', 'start': 210.722, 'duration': 7.034}, {'end': 224.605, 'text': 'New employee with ID is equal to 8 is added at so-and-so date and time.', 'start': 218.437, 'duration': 6.168}], 'summary': 'Capture audit information in tbl audit table when new employee is added to tbl employee table.', 'duration': 49.631, 'max_score': 174.974, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w174974.jpg'}, {'end': 298.553, 'src': 'embed', 'start': 246.996, 'weight': 1, 'content': [{'end': 253.218, 'text': 'as soon as you know, the insert statement is fired, you know is executed on this TBL employee table.', 'start': 246.996, 'duration': 6.222}, {'end': 258.666, 'text': 'I want this trigger after trigger, after insert trigger to be fired,', 'start': 253.218, 'duration': 5.448}, {'end': 271.417, 'text': 'which will capture the ID of the record that you have inserted into this table and then the date and time and log that or insert another row into this TBL employee audit table.', 'start': 258.666, 'duration': 12.751}, {'end': 272.518, 'text': "let's look at that in action.", 'start': 271.417, 'duration': 1.101}, {'end': 275.28, 'text': 'you know that would be much easier, right?', 'start': 272.518, 'duration': 2.762}, {'end': 276.921, 'text': 'so here we have a trigger.', 'start': 275.28, 'duration': 1.641}, {'end': 278.362, 'text': 'now look at this.', 'start': 276.921, 'duration': 1.441}, {'end': 283.386, 'text': "whenever you're creating a trigger, you create a trigger for a table and for a specific event.", 'start': 278.362, 'duration': 5.024}, {'end': 289.38, 'text': 'Now, for this example, we want to create a trigger on the stable TBL employee.', 'start': 284.682, 'duration': 4.698}, {'end': 298.553, 'text': 'for insert event because as soon as a new row is inserted into tblEmployee, we want to log the audit information.', 'start': 290.191, 'duration': 8.362}], 'summary': 'Trigger logs id, date, and time of new employee record insert into tbl employee table.', 'duration': 51.557, 'max_score': 246.996, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w246996.jpg'}, {'end': 344.738, 'src': 'embed', 'start': 320.99, 'weight': 6, 'content': [{'end': 328.353, 'text': 'Here we are creating a trigger for insert action on tblEmployee for insert as begin and end.', 'start': 320.99, 'duration': 7.363}, {'end': 331.014, 'text': 'This is just like stored procedure, so as begin and end.', 'start': 328.373, 'duration': 2.641}, {'end': 335.155, 'text': 'So inside your trigger, you can implement whatever logic you want.', 'start': 331.434, 'duration': 3.721}, {'end': 337.136, 'text': 'So what we want here?', 'start': 336.115, 'duration': 1.021}, {'end': 341.257, 'text': 'we want to capture the ID of the employee that we have just inserted into tblEmployee table.', 'start': 337.136, 'duration': 4.121}, {'end': 344.738, 'text': "So let's declare a variable of type integer.", 'start': 341.656, 'duration': 3.082}], 'summary': 'Creating trigger for insert action on tblemployee to capture employee id.', 'duration': 23.748, 'max_score': 320.99, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w320990.jpg'}, {'end': 440.818, 'src': 'heatmap', 'start': 413.524, 'weight': 1, 'content': [{'end': 417.925, 'text': 'So we are creating a trigger here on the employee table for insert action.', 'start': 413.524, 'duration': 4.401}, {'end': 424.688, 'text': 'And what is this trigger doing? This trigger is selecting all the columns and rows from a table called inserted.', 'start': 417.965, 'duration': 6.723}, {'end': 427.729, 'text': "So in a bit, we'll understand what this table is.", 'start': 425.528, 'duration': 2.201}, {'end': 429.31, 'text': "So let's create this trigger first.", 'start': 427.789, 'duration': 1.521}, {'end': 431.431, 'text': 'So create trigger, trigger name.', 'start': 429.75, 'duration': 1.681}, {'end': 434.357, 'text': 'execute command completed successfully.', 'start': 432.597, 'duration': 1.76}, {'end': 436.098, 'text': 'so this trigger is created.', 'start': 434.357, 'duration': 1.741}, {'end': 438.898, 'text': 'so we created this trigger on tbl employee table.', 'start': 436.098, 'duration': 2.8}, {'end': 440.818, 'text': 'so go to tbl employee table.', 'start': 438.898, 'duration': 1.92}], 'summary': 'A trigger was created on the employee table for insert action to select all columns and rows from a table called inserted.', 'duration': 27.294, 'max_score': 413.524, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w413524.jpg'}, {'end': 543.727, 'src': 'heatmap', 'start': 464.8, 'weight': 0.792, 'content': [{'end': 468.702, 'text': "Let's look at what's going to be there in inserted table.", 'start': 464.8, 'duration': 3.902}, {'end': 471.984, 'text': 'I press F5.', 'start': 470.603, 'duration': 1.381}, {'end': 473.225, 'text': "Look at what's happening.", 'start': 472.084, 'duration': 1.141}, {'end': 481.41, 'text': 'So when you execute an insert into TBL values, you are actually inserting a row into this TBL employee table.', 'start': 474.345, 'duration': 7.065}, {'end': 489.735, 'text': 'When you did that, so this row with ID is equal to 8, that is inserted into TBL employee table, which is good.', 'start': 481.83, 'duration': 7.905}, {'end': 495.572, 'text': 'okay, but then look at this it has selected all the rows and columns in the trigger.', 'start': 490.43, 'duration': 5.142}, {'end': 497.513, 'text': 'you told okay, select star from inserted.', 'start': 495.572, 'duration': 1.941}, {'end': 502.655, 'text': 'so it has selected that row from a table called inserted.', 'start': 497.513, 'duration': 5.142}, {'end': 506.056, 'text': "okay, and then let's actually do this.", 'start': 502.655, 'duration': 3.401}, {'end': 511.898, 'text': 'so so if you look at this, id is equal to 8, name, gene, salaries so and so, and the gender is female,', 'start': 506.056, 'duration': 5.842}, {'end': 514.438, 'text': "and let's actually select from the employee table.", 'start': 511.898, 'duration': 2.54}, {'end': 518.458, 'text': "So if you look at the employee table, it's exactly a copy of that row.", 'start': 515.177, 'duration': 3.281}, {'end': 527.463, 'text': 'So what is this inserted table? Where did this come from? Now, inserted table is a special table that is used by triggers.', 'start': 518.779, 'duration': 8.684}, {'end': 531.865, 'text': 'And this table is only available in the context of a trigger.', 'start': 528.663, 'duration': 3.202}, {'end': 543.727, 'text': 'Now, whenever you insert a row into tblEmployee, or any other table for that matter, SQL Server maintains this magic table called insertedTable,', 'start': 533.183, 'duration': 10.544}], 'summary': 'Inserting a row with id 8 into tbl employee table triggers the selection of all rows and columns, including a special table called inserted, which is only available in the context of a trigger.', 'duration': 78.927, 'max_score': 464.8, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w464800.jpg'}, {'end': 543.727, 'src': 'embed', 'start': 518.779, 'weight': 7, 'content': [{'end': 527.463, 'text': 'So what is this inserted table? Where did this come from? Now, inserted table is a special table that is used by triggers.', 'start': 518.779, 'duration': 8.684}, {'end': 531.865, 'text': 'And this table is only available in the context of a trigger.', 'start': 528.663, 'duration': 3.202}, {'end': 543.727, 'text': 'Now, whenever you insert a row into tblEmployee, or any other table for that matter, SQL Server maintains this magic table called insertedTable,', 'start': 533.183, 'duration': 10.544}], 'summary': 'Inserted table is a special table used by triggers in sql server.', 'duration': 24.948, 'max_score': 518.779, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w518779.jpg'}, {'end': 619.114, 'src': 'embed', 'start': 594.656, 'weight': 8, 'content': [{'end': 603.244, 'text': 'And if you remember, the inserted table structure was identical to the structure of this TBL employee table.', 'start': 594.656, 'duration': 8.588}, {'end': 613.152, 'text': 'Two things to keep in mind, the inserted table structure is identical to the structure of the table on the on clause here, in this case tblEmployee.', 'start': 604.09, 'duration': 9.062}, {'end': 619.114, 'text': "And this inserted table, it's a special table created by SQL Server for the purposes of triggers.", 'start': 613.632, 'duration': 5.482}], 'summary': 'The inserted table structure matches tbl employee table, a special table created by sql server for triggers.', 'duration': 24.458, 'max_score': 594.656, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w594656.jpg'}], 'start': 174.974, 'title': 'Implementing automatic audit logging', 'summary': "Discusses setting up automatic audit logging for the tblemployee table, ensuring accountability and traceability for each addition. it also covers the implementation of a trigger to capture the id of the record, date, and time of insertion, and log it into the tbl employee audit table, as well as creating a trigger to audit the insertion of new employees into the 'tbl employee' table.", 'chapters': [{'end': 224.605, 'start': 174.974, 'title': 'Automatic audit logging for employee table', 'summary': 'Discusses setting up automatic audit logging for the tblemployee table, where insertion of new employee records triggers the capture of audit information in the tblaudit table, ensuring accountability and traceability for each addition.', 'duration': 49.631, 'highlights': ['Setting up automatic audit logging for the tblEmployee table to capture audit information in the tblAudit table upon insertion of new employee records, enabling traceability and accountability.', 'Describing the structure of the tblEmployee and tblAudit tables, illustrating the process of capturing audit data and ensuring data integrity.', 'Illustrating the specific audit message format for new employee additions, including the employee ID, date, and time of insertion.']}, {'end': 368.91, 'start': 225.526, 'title': 'Implementing trigger for audit logging', 'summary': 'Discusses implementing a trigger for the tbl employee table to capture the id of the record, date, and time of insertion, and log it into the tbl employee audit table, demonstrating the creation and implementation of the trigger.', 'duration': 143.384, 'highlights': ['The chapter demonstrates the creation of a trigger to capture the ID of the employee record, date, and time upon insertion into the TBL employee table and log this information into the TBL employee audit table, providing a mechanism for audit logging.', 'The trigger is created for the TBL employee table and specifically for the insert event to log the audit information whenever a new row is inserted into the TBL employee table, ensuring accurate and timely capture of the required data.', "A variable of type integer is declared to capture the ID of the employee inserted into the TBL employee table, utilizing the 'inserted' table to access the inserted data and retrieve the ID of the person, facilitating seamless integration of the trigger and audit logging process."]}, {'end': 774.626, 'start': 368.93, 'title': 'Creating trigger for auditing new employee insertion', 'summary': "Discusses creating a trigger to audit the insertion of new employees into the 'tbl employee' table, utilizing the 'inserted' table within the context of a trigger to capture audit information and demonstrating the structure and usage of the 'inserted' table.", 'duration': 405.696, 'highlights': ["The 'inserted' table is a special table used by triggers to maintain a copy of the row that has been inserted into the 'TBL employee' table, allowing for access within the trigger context to inspect values, roll back changes, or capture audit information.", "The trigger creates a variable to retrieve the ID from the 'inserted' table, and then inserts audit information into the 'TBL employee audit' table, capturing the message 'New employee with ID' concatenated with the retrieved ID and the current date and time.", "The structure of the 'inserted' table is identical to the structure of the table specified in the trigger's 'on' clause, and it is a special table created by SQL Server solely for the purpose of triggers, exclusively available within the trigger context."]}], 'duration': 599.652, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w174974.jpg', 'highlights': ['Setting up automatic audit logging for the tblEmployee table to capture audit information in the tblAudit table upon insertion of new employee records, enabling traceability and accountability.', 'The chapter demonstrates the creation of a trigger to capture the ID of the employee record, date, and time upon insertion into the TBL employee table and log this information into the TBL employee audit table, providing a mechanism for audit logging.', 'Describing the structure of the tblEmployee and tblAudit tables, illustrating the process of capturing audit data and ensuring data integrity.', 'The trigger is created for the TBL employee table and specifically for the insert event to log the audit information whenever a new row is inserted into the TBL employee table, ensuring accurate and timely capture of the required data.', 'Illustrating the specific audit message format for new employee additions, including the employee ID, date, and time of insertion.', "The trigger creates a variable to retrieve the ID from the 'inserted' table, and then inserts audit information into the 'TBL employee audit' table, capturing the message 'New employee with ID' concatenated with the retrieved ID and the current date and time.", "A variable of type integer is declared to capture the ID of the employee inserted into the TBL employee table, utilizing the 'inserted' table to access the inserted data and retrieve the ID of the person, facilitating seamless integration of the trigger and audit logging process.", "The 'inserted' table is a special table used by triggers to maintain a copy of the row that has been inserted into the 'TBL employee' table, allowing for access within the trigger context to inspect values, roll back changes, or capture audit information.", "The structure of the 'inserted' table is identical to the structure of the table specified in the trigger's 'on' clause, and it is a special table created by SQL Server solely for the purpose of triggers, exclusively available within the trigger context."]}, {'end': 1063.765, 'segs': [{'end': 847.981, 'src': 'embed', 'start': 797.179, 'weight': 0, 'content': [{'end': 803.884, 'text': 'So immediately after a row is inserted into TBL employee table, this trigger gets fired.', 'start': 797.179, 'duration': 6.705}, {'end': 809.428, 'text': 'And we are saying in this trigger, insert an audit row into TBL employee audit table.', 'start': 804.724, 'duration': 4.704}, {'end': 810.929, 'text': "So let's execute that.", 'start': 809.768, 'duration': 1.161}, {'end': 813.651, 'text': 'Look at this, one row affected, one row affected.', 'start': 811.469, 'duration': 2.182}, {'end': 815.753, 'text': 'This one got into TBL employee table.', 'start': 813.711, 'duration': 2.042}, {'end': 818.775, 'text': 'This one got into TBL employee audit table.', 'start': 816.213, 'duration': 2.562}, {'end': 822.798, 'text': "Now let's select everything from TBL employee and TBL employee audit.", 'start': 819.095, 'duration': 3.703}, {'end': 831.609, 'text': 'and you should see in the audit information, new employee with ID is equal to 9, is added at so and so date.', 'start': 823.382, 'duration': 8.227}, {'end': 838.254, 'text': "okay. so now let's look at a delete trigger after delete trigger.", 'start': 831.609, 'duration': 6.645}, {'end': 841.937, 'text': 'okay, so basically exactly the same thing.', 'start': 838.254, 'duration': 3.683}, {'end': 847.981, 'text': 'now we have captured the audit information when we added a new employee to this table.', 'start': 841.937, 'duration': 6.044}], 'summary': 'Trigger captures audit info for employee table changes.', 'duration': 50.802, 'max_score': 797.179, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w797179.jpg'}, {'end': 1054.317, 'src': 'embed', 'start': 996.504, 'weight': 2, 'content': [{'end': 1003.751, 'text': 'This one is deleted from employee and this one row affected is the row that got inserted into TBL employee audit table.', 'start': 996.504, 'duration': 7.247}, {'end': 1007.434, 'text': 'So we should have the audit information captured there.', 'start': 1004.111, 'duration': 3.323}, {'end': 1014.659, 'text': 'So an existing employee, if you look at this with ID is equal to 1, is deleted at so and so date and time.', 'start': 1008.737, 'duration': 5.922}, {'end': 1025.801, 'text': 'So this is an after insert trigger, after delete trigger.', 'start': 1017.62, 'duration': 8.181}, {'end': 1032.425, 'text': 'They are very much identical except that here you specify for delete and here you say for insert.', 'start': 1026.443, 'duration': 5.982}, {'end': 1041.728, 'text': 'And in the after insert trigger, you make use of inserted table and after delete trigger, you make use of deleted table.', 'start': 1033.204, 'duration': 8.524}, {'end': 1046.55, 'text': 'If it is an update, you make use of both inserted and updated tables.', 'start': 1042.167, 'duration': 4.383}, {'end': 1054.317, 'text': "We'll talk about updating and capturing audit information when you update a row in our next video session.", 'start': 1046.872, 'duration': 7.445}], 'summary': 'Employee audit information captured through after insert and delete triggers.', 'duration': 57.813, 'max_score': 996.504, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w996504.jpg'}], 'start': 775.927, 'title': 'Sql triggers for audit information', 'summary': 'Covers the implementation of insert and delete triggers in sql, showcasing the process of capturing audit information for new employee additions and deletions from the tblemployee table, resulting in successful insertion and deletion of records and the retrieval of audit information. it also demonstrates the process of creating triggers in sql server to capture audit information for insert and delete operations, including the use of meaningful trigger names and the utilization of inserted and deleted tables; a row is affected after executing a delete statement.', 'chapters': [{'end': 948.837, 'start': 775.927, 'title': 'Sql triggers for insert and delete operations', 'summary': 'Covers the implementation of insert and delete triggers in sql, showcasing the process of capturing audit information for new employee additions and deletions from the tblemployee table, resulting in successful insertion and deletion of records and the retrieval of audit information.', 'duration': 172.91, 'highlights': ['The trigger successfully inserts a new record into the tblEmployee table, resulting in one row affected, and concurrently inserts an audit row into the tblEmployee audit table.', 'The trigger captures information for new employee additions, displaying the audit information for the new employee with ID equal to 9, added on a specific date.', "The after delete trigger is implemented to capture audit information when an employee is deleted, utilizing the deleted table to maintain a copy of the deleted row's structure and retrieving the ID of the deleted record for audit purposes."]}, {'end': 1063.765, 'start': 949.537, 'title': 'Creating database triggers for audit information', 'summary': 'Demonstrates the process of creating triggers in sql server to capture audit information for insert and delete operations, including the use of meaningful trigger names and the utilization of inserted and deleted tables; a row is affected after executing a delete statement.', 'duration': 114.228, 'highlights': ['Triggers can be created in SQL Server to capture audit information for insert and delete operations, with meaningful trigger names providing the advantage of easily identifying the purpose of each trigger.', 'After executing a delete statement, one row is affected, and the audit information is captured in the TBL employee audit table.', 'In creating triggers for insert and delete operations, the after insert trigger makes use of the inserted table, while the after delete trigger makes use of the deleted table.']}], 'duration': 287.838, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/k0S4P-a6d5w/pics/k0S4P-a6d5w775927.jpg', 'highlights': ['The trigger successfully inserts a new record into the tblEmployee table, resulting in one row affected, and concurrently inserts an audit row into the tblEmployee audit table.', 'The trigger captures information for new employee additions, displaying the audit information for the new employee with ID equal to 9, added on a specific date.', "The after delete trigger is implemented to capture audit information when an employee is deleted, utilizing the deleted table to maintain a copy of the deleted row's structure and retrieving the ID of the deleted record for audit purposes.", 'Triggers can be created in SQL Server to capture audit information for insert and delete operations, with meaningful trigger names providing the advantage of easily identifying the purpose of each trigger.', 'After executing a delete statement, one row is affected, and the audit information is captured in the TBL employee audit table.', 'In creating triggers for insert and delete operations, the after insert trigger makes use of the inserted table, while the after delete trigger makes use of the deleted table.']}], 'highlights': ['Automatic audit logging for tblEmployee captures new employee additions and deletions', 'DML triggers classified into after and instead of triggers', 'After triggers fire after insert, update, or delete actions', 'Trigger captures ID, date, and time upon insertion into tblEmployee table', 'Inserted table maintains a copy of the inserted row for trigger access', 'After delete trigger captures audit information for deleted employees', 'Meaningful trigger names provide easy identification of their purpose', 'After insert trigger uses inserted table, after delete trigger uses deleted table', 'Trigger successfully inserts new record into tblEmployee table']}