1 00:01.190 --> 00:02.540 So let's start. 2 00:02.540 --> 00:06.620 We have to see the triggers today as much as possible. 3 00:07.460 --> 00:08.750 So what is a trigger? 4 00:08.780 --> 00:14.690 A trigger is a database object that is executed automatically when an event occurs in DB. 5 00:15.530 --> 00:23.810 A trigger is a special type of stored procedure that automatically executes or runs when an event occurs 6 00:23.810 --> 00:25.460 in database server. 7 00:26.390 --> 00:28.040 Now what are the different types? 8 00:28.070 --> 00:36.680 Triggers are created to control DML, DML, DDL to enforce complex rules and validations, and to audit 9 00:36.710 --> 00:37.610 tables. 10 00:38.330 --> 00:46.550 Now in SQL server there are mainly two kinds of triggers are there we can say DML triggers and DDL triggers 11 00:46.550 --> 00:48.020 are mostly used. 12 00:49.220 --> 00:51.650 Now what is a DML trigger? 13 00:51.650 --> 00:53.120 So it runs fires. 14 00:53.120 --> 01:01.590 When a user tries to modify data through a DML event like insert, update, delete in a table or view. 15 01:01.620 --> 01:02.250 Right. 16 01:02.580 --> 01:07.830 Insert update delete in a table or view and then DDL triggers so it runs fires. 17 01:07.830 --> 01:15.990 In response to DDL events like create, alter, drop and some stored procedure that performs DDL. 18 01:17.040 --> 01:17.910 Now let's see. 19 01:17.940 --> 01:20.160 First the DDL trigger. 20 01:20.580 --> 01:23.490 So see DDL triggers fire. 21 01:23.490 --> 01:28.530 In a response to a variety of DDL events like what are the DDL events are there. 22 01:28.740 --> 01:29.400 Create. 23 01:29.430 --> 01:29.880 Alter. 24 01:29.880 --> 01:30.570 Drop. 25 01:30.870 --> 01:31.620 Grant. 26 01:31.650 --> 01:32.010 Deny. 27 01:32.040 --> 01:32.760 Revoke. 28 01:33.810 --> 01:39.810 Even certain stored procedures that perform DDL, DDL operations. 29 01:39.870 --> 01:43.740 We can use DDL triggers when we want to do the following. 30 01:43.890 --> 01:47.490 Prevent someone some changes in the database. 31 01:47.490 --> 01:51.750 Now suppose I am an administrator, right? 32 01:51.750 --> 01:57.280 And I don't want to perform any kind of DDL statement by any of the user. 33 01:59.650 --> 02:05.620 Any of the user in the database at that time, I can create a DDL trigger. 34 02:06.040 --> 02:09.040 Now record changes or events in the database schema. 35 02:09.070 --> 02:14.710 Then have something to occur in the database in response to change in database schema. 36 02:17.290 --> 02:23.620 So let's see the example of DDL trigger. 37 02:23.650 --> 02:28.720 The DDL triggers can be created in a specific database or at a server level. 38 02:28.780 --> 02:34.510 If we set the scope to server level, then it is applied to all the databases of that server. 39 02:35.260 --> 02:38.320 Now see I have created this particular trigger. 40 02:38.350 --> 02:39.040 Create trigger. 41 02:39.070 --> 02:40.840 Trigger name on database. 42 02:41.380 --> 02:49.630 On database means on Udemy database right on Udemy database I have created this trigger on Udemy database 43 02:49.630 --> 02:54.910 for create table on database for create table. 44 02:54.920 --> 02:56.870 Now what I'm printing over here. 45 02:57.050 --> 02:58.850 You cannot create a. 46 03:05.720 --> 03:08.600 You cannot create a table on this database. 47 03:08.600 --> 03:08.870 Right. 48 03:08.870 --> 03:11.810 You cannot create a table on this database. 49 03:13.610 --> 03:15.230 It is showing me something. 50 03:15.230 --> 03:17.180 So now let me try to create. 51 03:17.210 --> 03:20.390 Now I have actually already created this, uh trigger. 52 03:20.390 --> 03:22.250 Let me try to create table. 53 03:28.430 --> 03:32.780 To show you see see what error it is throwing. 54 03:38.240 --> 03:41.810 You cannot create a table on this database. 55 03:41.810 --> 03:42.200 Right? 56 03:42.200 --> 03:44.570 That is what we are printing over here. 57 03:45.350 --> 03:47.300 That is what we are printing over here. 58 03:47.330 --> 03:47.960 Now. 59 03:47.960 --> 03:49.640 Same thing we can do over here. 60 03:49.670 --> 03:54.180 Create a trigger that will restrict altering operations on a specific database. 61 03:55.140 --> 03:57.300 So alter table above. 62 03:57.300 --> 03:57.990 It is a create. 63 03:58.020 --> 03:58.350 Table. 64 03:58.350 --> 04:00.120 Now it is a alter table. 65 04:00.210 --> 04:03.240 So now it will not allow me to alter as well. 66 04:03.270 --> 04:07.500 See if I specify this statement then. 67 04:07.530 --> 04:11.010 See you cannot alter a table on this database right. 68 04:11.400 --> 04:14.820 You cannot alter a table on this database. 69 04:16.620 --> 04:18.900 Now drop table for drop table. 70 04:18.900 --> 04:20.550 Also same thing will be applied right. 71 04:20.580 --> 04:22.230 Dot drop table dept. 72 04:22.260 --> 04:24.000 Let me try to drop the table. 73 04:24.180 --> 04:28.470 You cannot drop a table on this database right. 74 04:30.630 --> 04:34.920 So these are the mainly DDL triggers are there. 75 04:36.570 --> 04:38.640 Let me know if you have any doubt. 76 04:38.670 --> 04:40.950 Only one person is there today. 77 04:47.010 --> 04:48.390 No doubt right. 78 04:51.430 --> 04:54.610 So now let's see the DML triggers quickly. 79 05:02.170 --> 05:06.430 So it runs fires when it runs or fires. 80 05:09.040 --> 05:15.970 When a user tries to modify data through a DML event like insert, update delete on a table. 81 05:16.300 --> 05:16.810 Insert. 82 05:16.840 --> 05:17.260 Update. 83 05:17.260 --> 05:17.620 Delete. 84 05:17.650 --> 05:18.760 Owner table. 85 05:19.810 --> 05:22.090 Now DML triggers. 86 05:24.790 --> 05:31.630 It specifies that a DML trigger fires only when all operations specified in trigger SQL statement have 87 05:31.630 --> 05:33.100 launched successfully. 88 05:34.150 --> 05:36.760 Okay, now let me try to create. 89 05:36.790 --> 05:37.600 Okay. 90 05:38.440 --> 05:39.820 This is a DML trigger. 91 05:39.820 --> 05:44.800 Let me try to create this this trigger insert update delete on Sunday. 92 05:44.860 --> 05:48.700 So now see for which purpose I am creating this trigger. 93 05:49.430 --> 05:49.910 Create. 94 05:49.940 --> 05:50.210 Trigger. 95 05:50.240 --> 05:50.990 Trigger. 96 05:51.110 --> 05:51.620 Trigger. 97 05:51.650 --> 05:52.400 Name on. 98 05:52.400 --> 05:53.150 On which table? 99 05:53.150 --> 05:53.930 I want to create. 100 05:53.960 --> 05:56.240 Trigger on EMP table. 101 05:56.240 --> 05:57.410 I want to create a trigger. 102 05:57.410 --> 05:58.040 Right. 103 05:58.040 --> 06:01.220 And for which operation I want to take care of. 104 06:01.220 --> 06:04.040 I want to take care of insert, update and delete. 105 06:04.970 --> 06:08.300 So these are the DML statement right now. 106 06:08.300 --> 06:12.950 I don't want if it is a two days which day. 107 06:12.980 --> 06:15.770 Suppose let me create it for Friday. 108 06:15.920 --> 06:22.070 So if it is a Friday I don't want to allow anyone to update anything, right? 109 06:22.070 --> 06:23.540 Any want to update anything. 110 06:23.540 --> 06:27.260 So let me try to create trigger. 111 06:29.240 --> 06:33.170 It is already there so I have to alter the trigger right? 112 06:34.280 --> 06:35.900 I have to alter the trigger. 113 06:35.900 --> 06:38.360 Let me alter okay. 114 06:47.010 --> 06:49.620 Incompatible object type. 115 06:49.650 --> 06:50.370 Oh. 116 06:57.780 --> 07:00.120 It is showing something like in up. 117 07:00.150 --> 07:02.010 Let me create a new object. 118 07:02.010 --> 07:11.370 Create table Emp1 id integer name worker of ten. 119 07:13.950 --> 07:17.820 Now this object should be. 120 07:18.120 --> 07:21.720 Oh, there is a problem in this database. 121 07:38.130 --> 07:39.180 Okay. 122 07:49.630 --> 07:51.160 Okay, let me try to create. 123 07:51.160 --> 07:56.080 I am not sure whether MP table is there or not. 124 07:56.110 --> 07:57.610 MP table does not exist. 125 07:57.640 --> 07:58.420 Okay. 126 08:02.440 --> 08:05.170 Let me try to create MP table. 127 08:05.560 --> 08:09.070 I don't know is in this entire server there is a problem. 128 08:10.060 --> 08:11.440 Okay, so I can. 129 08:15.850 --> 08:18.550 So you let me try to explain. 130 08:18.550 --> 08:20.800 You only I can't perform this operation. 131 08:20.800 --> 08:24.370 There is some error which is not in control. 132 08:26.680 --> 08:33.100 So by specifying creating this trigger, you can if it is a Friday, right? 133 08:33.100 --> 08:34.270 If it is a Friday. 134 08:34.270 --> 08:38.470 So it will check by using the date name function. 135 08:38.470 --> 08:44.900 And if it is a Friday, it will not allow you to either update in this particular table, or it will 136 08:44.900 --> 08:47.990 not allow you to insert or delete in this particular table. 137 08:47.990 --> 08:49.610 It will throw some error. 138 08:51.800 --> 08:53.480 It will throw some error. 139 08:54.320 --> 08:55.610 Now let's move ahead. 140 08:55.610 --> 08:56.960 How to alter the trigger. 141 08:56.990 --> 08:57.710 This is okay. 142 08:57.740 --> 08:58.610 This is simple. 143 08:58.610 --> 09:00.200 Alter trigger trigger one. 144 09:00.230 --> 09:03.800 You just need to mention the alter keyword over here. 145 09:04.670 --> 09:07.700 You just need to mention alter keyword over here. 146 09:08.300 --> 09:14.750 Now create a trigger to not to delete on EMP one table as per following rule. 147 09:19.220 --> 09:20.660 As per following rule. 148 09:20.660 --> 09:26.660 So create trigger trigger one on EMP one after delete. 149 09:27.650 --> 09:35.180 Now over here we are checking the if condition date part okay. 150 09:35.210 --> 09:38.930 If it is a date of the week okay. 151 09:38.960 --> 09:42.700 If it is a date of the week between 2 and 6. 152 09:42.700 --> 09:45.760 So in a week we have 1 to 7 days. 153 09:45.790 --> 09:47.410 Are there, are there right? 154 09:47.440 --> 09:52.780 One means Sunday, then two means Monday like that. 155 09:52.780 --> 10:00.580 So if it is a day of the week between 2 and 6, what this query will give me, let me see what this 156 10:00.580 --> 10:01.750 query will give me. 157 10:02.050 --> 10:07.090 Select this one and just run this query. 158 10:07.870 --> 10:09.610 Today is Friday I think. 159 10:09.610 --> 10:11.740 So that is why it is giving six. 160 10:11.980 --> 10:17.890 So I think Sunday it is a zero, Sunday zero Monday one like that. 161 10:17.890 --> 10:24.070 So if it is 2 to 6 between then only allowed between. 162 10:25.690 --> 10:30.580 If this is a first condition then it is again this is the second condition. 163 10:31.780 --> 10:42.350 And if both of these condition are matching then it will not allow us any only allowed between 10 to 164 10:42.350 --> 10:42.650 4. 165 10:42.680 --> 10:45.470 Only 10 a.m. to 4 p.m. only. 166 10:47.000 --> 10:52.220 And if it is a date of the week is seven, then it will allow us. 167 10:52.970 --> 10:55.100 And and timing will be this one. 168 10:55.100 --> 10:57.740 Then there is some other condition. 169 10:57.740 --> 10:59.510 So you can mention like this. 170 10:59.510 --> 11:04.160 Also it is for actually not for insert and update. 171 11:04.160 --> 11:05.930 It is for delete only. 172 11:06.200 --> 11:09.230 If you see over here it is for delete only. 173 11:17.000 --> 11:17.510 Okay. 174 11:17.540 --> 11:18.590 One minute. 175 11:37.290 --> 11:40.050 Create a trigger not to allow update. 176 11:40.800 --> 11:47.160 Update the column amp number, but I think it will not allow me to create a table. 177 11:47.160 --> 11:48.510 Let me try. 178 11:48.510 --> 11:53.130 If I am able to create a table, I do not know what is this error. 179 11:54.780 --> 11:58.530 The problem is some error is coming on over here. 180 11:58.770 --> 12:06.810 Then I have to insert the data into this one and then create trigger three three after update. 181 12:07.110 --> 12:10.740 Now see what I am checking over here after update. 182 12:10.740 --> 12:14.370 So before update you can check as well as after update. 183 12:14.370 --> 12:26.430 You can check if update of AMP number not allow to update the column amp number then amp number column 184 12:26.430 --> 12:27.510 cannot be updated. 185 12:27.540 --> 12:37.060 See over here what I am checking after update if someone is trying to update the MP number column for 186 12:37.060 --> 12:39.040 some specific employee. 187 12:39.100 --> 12:45.370 For some specific employee, then I should not allow to update the employee number, right? 188 12:45.400 --> 12:51.940 Because in the company also, you will be assigned the unique employee number at once, right? 189 12:51.970 --> 12:54.580 Usually it is not able to change, right. 190 12:54.610 --> 12:56.320 Nobody will able to change. 191 12:56.800 --> 12:59.290 So this trigger is for same purpose. 192 13:00.670 --> 13:02.770 This trigger is for same purpose. 193 13:03.490 --> 13:06.880 We should not allow to update the MP number. 194 13:10.000 --> 13:11.950 Now what are the magic tables. 195 13:11.950 --> 13:19.930 So there are some magic tables available in the SQL which is specifically used while we are dealing 196 13:19.930 --> 13:21.220 with the triggers. 197 13:21.250 --> 13:33.350 So now these are the temporary logical tables which are created by SQL server when we execute any DML 198 13:33.350 --> 13:34.820 or DML command. 199 13:34.850 --> 13:35.120 Right. 200 13:35.150 --> 13:36.560 Insert, update or delete. 201 13:37.220 --> 13:42.110 Now say inserted is one magical table and deleted is another magical table. 202 13:43.640 --> 13:45.230 Inserted is one magical table. 203 13:45.230 --> 13:48.440 Deleted is another magical table within the trigger? 204 13:48.440 --> 13:49.220 We cannot. 205 13:49.250 --> 13:54.800 We can refer the to these two tables created by SQL server. 206 13:56.060 --> 14:03.380 It contains new rows for insert and update operations and contains original row for delete and update 207 14:03.410 --> 14:04.460 operations. 208 14:05.870 --> 14:07.670 Now let me try. 209 14:07.700 --> 14:18.860 So inserted will contain the new rows whereas deleted original row and update command original row before 210 14:18.860 --> 14:19.430 the update. 211 14:19.430 --> 14:21.350 Deleted rows and new rows. 212 14:26.090 --> 14:31.770 So create a trigger not to allow increment of salary in EMP one one table. 213 14:32.010 --> 14:33.390 So again. 214 14:36.030 --> 14:38.880 Over here we are trying to update the salary right. 215 14:38.910 --> 14:40.410 Of MP one one table. 216 14:40.410 --> 14:42.300 We are trying to update the salary. 217 14:42.390 --> 14:44.670 So new salary and old salary. 218 14:44.700 --> 14:45.930 We have to declare. 219 14:49.200 --> 14:49.560 Okay. 220 14:49.590 --> 14:51.420 This is a decrement of salary right. 221 14:51.420 --> 14:53.430 We are checking decrement of salary. 222 14:53.580 --> 14:56.220 So new salary I have to consider. 223 14:56.250 --> 15:00.000 And I have to store the old salary as well somewhere. 224 15:00.030 --> 15:01.560 So new salary I have. 225 15:02.910 --> 15:04.410 This is a variable. 226 15:04.410 --> 15:10.260 So salary column will be stored into this particular new salary variable. 227 15:11.160 --> 15:18.090 And this old salary from the deleted will be stored into this old salary table. 228 15:18.120 --> 15:25.830 Now if new salary is less than old salary means updated salary is less than old salary. 229 15:26.430 --> 15:29.010 That means it is a decrement of salary, right? 230 15:29.020 --> 15:31.720 That means it is a decrement of salary. 231 15:37.090 --> 15:38.620 Then rollback transaction. 232 15:38.620 --> 15:43.360 Then we have to rollback transaction raise error because it is not possible. 233 15:43.390 --> 15:43.690 Right. 234 15:43.690 --> 15:45.850 Because it is not possible. 235 15:49.240 --> 15:51.160 Now what is this. 236 15:52.810 --> 16:02.770 Delete a insert m delete create trigger to insert employee detail into some table whenever employee 237 16:02.770 --> 16:04.780 resigns from the organization. 238 16:05.350 --> 16:09.040 So this kind of trigger also you can able to create. 239 16:16.570 --> 16:21.340 There is one employee resign table I have created and this is a trigger. 240 16:21.340 --> 16:27.890 So from this particular employee after delete after delete I have to store the data into this rejoined 241 16:27.890 --> 16:28.190 table. 242 16:28.220 --> 16:28.820 Right? 243 16:29.420 --> 16:34.430 So for that purpose, all the variables which are available, I have to declare. 244 16:35.450 --> 16:45.230 And then from the deleted table, I have stored this for all four columns into all four variables. 245 16:45.590 --> 16:51.170 And now I am inserted into this table right from this particular variable. 246 16:51.560 --> 16:58.550 So whenever delete will happen on this table, data will be stored in the deleted table automatically. 247 16:58.880 --> 17:07.130 And from the date deleted table all this all the record will be stored into this employee resigned table. 248 17:07.790 --> 17:14.600 So when we are trying to delete the employee over here manually at that time, this thing will be happen 249 17:14.600 --> 17:15.620 automatically. 250 17:20.360 --> 17:21.080 Right. 251 17:24.110 --> 17:25.880 So let me ask.