SQL Server trigger cursor note today receives a requirement to use a trigger to update other tables through conditions. I haven't touched SQLSERVER for a long time, and there is no SQLSERVER installation package in my computer. My colleague sent an installation package. I was shocked, 3.6 GB !!!! After a long wait, start the job. Requirements: 1. When field a is updated to 2 or 3, and field B is
SQL SERVER trigger cursor note today receives a requirement to use a trigger to update other tables through conditions. I haven't touched SQL server for a long time, and I haven't installed the SQL server Installation Package on my computer. My colleague sent an installation package. I was shocked, 3.6 GB !!!! After a long wait, start the job. Requirements: 1. When field a is updated to 2 or 3, and field B is
SQL SERVER trigger cursor note
Today, we receive a request to use a trigger to update other tables through conditions. I haven't touched SQL server for a long time, and I haven't installed the SQL server Installation Package on my computer. My colleague sent an installation package. I was shocked, 3.6 GB !!!! After a long wait, start the job. The requirements are as follows:
1. When field a is updated to 2 or 3 and field B is updated to y, insert the id and Q
2. When field a is updated to 3 and field B is updated to n, insert the table id in the new table.
The Code is as follows:
Create trigger updateExangeon [dbo]. [EXAM_MASTER] after updateasif (exists (select inserted. result_status, inserted. consultation_status from inserted where (inserted. result_status = '2' or inserted. result_status = '3') and consultation_status = 'y') begindeclare id_cursor1 cursor forselect inserted. exam_id from insertedopen id_cursor1declare @ exam_id int -- @ exam_id must be the same as the field name in the cursor fetch next from id_cursor1 into @ exam_idwhile @ FETCH_STATUS = 0 begininsert into [dbo]. [Exchange] (id, examid, mark) values (NEWID (), @ exam_id, 'q') fetch next from id_cursor1 into @ exam_idendclose id_cursor1deallocate id_cursor1endif (exists (select inserted. result_status, inserted. consultation_status from inserted where inserted. result_status = '3' and consultation_status = 'n') begin declare id_cursor2 cursor forselect inserted. exam_id from insertedopen id_cursor2fetch next from id_cursor2 into @ exam_idwhile @ FETCH_STATUS = 0 begininsert into [dbo]. [Exchange] (id, examid, mark) values (NEWID (), @ exam_id, 'A') fetch next from id_cursor2 into @ exam_idendclose id_cursor2deallocate id_cursor2end
Although it is not the best way, the task is completed.
Posted on