Saturday, May 16, 2015

SQL 2005 - SQL Trigger dealing with multiple rows

1. SQL Triggers

A “Trigger” is a compiled unit of SQL server procedure, which can run automatically when an event occurs on the database objects. For example, you can write a piece of SQL Script (the trigger), which can be called whenever an insert (the event) happens on a specific table. There are different type of triggers available in SQL Server. The two important types are:

1) DDL Trigger: These triggers act on “Data Definition Language”; say for example a trigger procedure runs whenever a table is created. We can say DDL trigger as “Database level trigger” as it acts on the Database. The Trigger procedure can be invoked whenever the manipulation like Create, Alter and Drop takes place on database objects.

2) DML Trigger: These triggers act on “Data Manipulation Language”. In Database world, the tables maintain the Data. So manipulating data can be done through the tables by making use of Insert, Update and Delete statements on the table. So we can call DML trigger as "Table level trigger" since it operates at table level.

  1. DDL Triggers are at Database level and acts on DB Object (Table, views, procedure etc.) Manipulation (Create, Alter, Drop etc.)
  2. DML Triggers are at Table level and acts on Table data (Record) manipulation (Insert, Update, Delete)
The below screen shot shows both the triggers on Management studio:

Figure 1: Table Trigger, Database Trigger
Figure 1: Table Trigger, Database Trigger

In the above screen shot, Chief_NoDelete is a Table level trigger and the trigger fires when an attempt to delete a record on employee table is made. Similarly, StopViewDelete is a database level trigger, which will get fired whenever an attempt to drop a database view is made.

In this article, we will explore DML and DDL Triggers. You can use Microsoft supplied Pubs Sample database (Script available in SQL2005 Page) for all the code example.

2. DDL Trigger Example

Let us create a DDL trigger so that dropping view is prohibited. Have a look at the picture below:

Figure 2: Creating a DDL Trigger
Figure 2: Creating a DDL Trigger

In the code snippet, StopViewDelete is the Trigger Name and the trigger is created for "DROP_VIEW" event. The “ON DATABASE” states the trigger is at database level as the dropping a view has the impact at Database level. Execute the script, to create the trigger. Script code is given below:

-- 1. Simple DDL Trigger
--DDL trigger
   PRINT 'Contact Administrator to Drop view';

Once the database trigger StopViewDelete is created, the action of dropping a view is stopped with the indication that administrator needs to be contacted. The below SQL statement tries to delete the view:

--Attempt to delete a view
drop view titleview;

From the Messages tab of the SQL Server management studio, you can see result of executing the above statement. The screenshot of the message "Msg 3609, Level 14" is shown below:

Figure 3: Trigger Rollbacks the View Drop
Figure 3: Trigger Rollbacks the View Drop
Dropping a database level trigger is slightly different from dropping a normal trigger. To drop a database level trigger, place a keyword “ON Database” at the end of the trigger-drop statement.  The below script shows dropping a trigger that we created:

--Drop the Trigger. Note On Database keyword
Drop Trigger StopViewDelete On Database;

3. DML AFTER Trigger Example – On Deleting a Record

Now we will create a "DML Trigger", which will act at table level. From “Figure. 1” we can see that the trigger object is placed under the table node. Like Table constraints, Table index; DML Trigger is also table level object. The below picture shows a simple table level trigger:

Figure 4: DML Trigger After Delete
Figure 4: DML Trigger After Delete

In the above example, we are creating a trigger named “Chief_NoDelete” (Marked as 1). The purpose of this trigger is to avoid deletion of employee who has higher position and the positions are ‘Chief Executive Officer’, ‘Chief Financial Officer’. The ‘On Employee’ (Marked as 2) in the script shows that the trigger operates on the employee table. Once we specified table name, we need to specify triggering event and in our example we specified deleting a record as the triggering event. The “FOR Delete” (Marked as 3) keyword tells that the trigger will get fired when a delete statement is issued on the Employee table. Like Delete, one can create trigger for Insert as well as Update. This also tells that the trigger is “AFTER TRIGGER”. After triggers are fired once the DML statement is executed.

Once we specify the Trigger Event and Event Origin (In our case employee table), we can start writing the scripting code that defines how we want to respond to the occurrence of the event on the Employee table. In the above example, Job_Id of the record to be deleted is taken and stored in the @JobID local variable. When Job_Id is either 2 or 4 the employee is considered as chief and deletion of the record is skipped at trigger level. Note the Rollback statement after the message print. As this is an ‘AFTER TRIGGER’ and the deletion was already made, we have to rollback that. In the script you can see Job ID is taken from table called “Deleted”(Marked as 4). SQL Server maintains two virtual tables named “INSERTED”, “DELETED” and we will explore that in more detail in the later part of the article. For now you can see the job_id that is part of the record marked for deletion is supplied by the Deleted virtual table. Now, when we will try to delete a chief employee by executing the below SQL statement:

Delete from Employee where lname = 'Chang';

The employee we are attempting to delete is a chief employee and this is shown in the below screen shot:

Figure 5: Chief Employees
Figure 5: Chief Employees

OK, as the employee is ‘Chief Financial Officer’ the attempt to delete the record is stopped at trigger level by making use the rollback statement. Have a look at the message below:

Chief Employee Can not be Deleted
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

The message states that the deletion is cancelled at the trigger ‘Chief_NoDelete’. Note that in SQL server, triggers are invoked per statement. In our case the delete statement on employee table fired the trigger, and in the trigger body the rollback statement considers that delete statement as the transaction and reverts that back to the original state.

The trigger we wrote won’t work for delete statement that affects multiple records. We will see that in detail towards the end of the article.

4. INSERTED and DELETED virtual tables

In the previous example we saw a table called deleted. SQL Server maintains two virtual tables named “INSERTED” and “DELETED”. When there exists a trigger for the table say <ABC>, SQL server populates the virtual table while executing the DML statements on that table <ABC>. Have a look at the below picture:

  Figure 6: Inserted and deleted Virtual table entries
Figure 6: Inserted and deleted Virtual table entries

The first table in the left shows that inserted virtual table having valid entries. This is because the insert statement newly introduces values for the database table. Similarly, the table in the middle shows Virtual table entries for the Delete statement and here we see Deleted virtual table has entries and insert is empty. In case of update (The right most one), for a particular column in record, we may end up value before the update (Deleted) and value after the update (Inserted) and that is why we have both ‘Inserted’ and ‘updated’ virtual table gets populated.

5. Instead of Trigger – For Delete

Previously we saw “AFTER TRIGGER”, which get fired once the execution of DML statement is finished. OK. The “INSTEAD OF TRIGGER” replaces the actual DML statement that fired the trigger. Confusing? For example, say table ABC has an instead of trigger in it, executing the Insert statement on the table ABC is replaced by the Trigger. That is ‘InStead of’ actual insert statement, the trigger get fired. Got it right?

Below is the example for “Instead of Trigger” on Delete statement:

Figure 7: Instead of Trigger
Figure 7: Instead of Trigger

The Instead of trigger is created the same way  after trigger got created. The only difference is in the syntax which uses the keyword “Instead of” (Marked as 1) replacing the “For”.  As the trigger replaces the Delete statement, we took employee id value from the ‘deleted’ virtual table (Marked as 2) and fired the delete statement again by introducing that value in the where clause(Marked as 3). As in the previous “AFTER TRIGGER” example, the “INSTEAD OF” trigger example also works for a delete operation that affects single row. Now execute the below statement which tries to delete a single record:

Delete from Employee where lname = 'Chang';

The result of executing the above statement is shown in the below screen shot:

Figure 8: Result of Delete statement
Figure 8: Result of Delete statement

First you see here a message printed, stating that the ‘Instead-of trigger fired’ (Marked as 1), which comes from the trigger Chief_Delete_Instead. The message marked as 2 is coming from the trigger ‘After Trigger’ as we are trying to delete the chief employee. The error message marked as 3 in the above picture, states that delete statement is executed from the “Instead-of” trigger at line 7. Since the employee is chief employee, the ‘After trigger’ rollbacks the delete statement invoked by the ‘Instead of’ trigger (Marked as 4).

In Summary (In the perspective delete statement we attempted from the management studio),

  1. Employee table has two triggers. One is a “Instead of Trigger” (Chief_Delete_Instead) and other one is “After Trigger” (Chief_NoDelete)
  2. When we execute the Delete statement, after making the virtual table entries, Instead of the actual Delete statement, the trigger Chief_Delete_Instead is invoked
  3. After printing Message (Just for Demo), It invokes the delete statement (SQL Server avoids the recursion here)
  4. Now, the after trigger is fired and roles back the transaction made by the Instead of trigger.

To add more explanation to the above summary, have a look at the below picture to understand execution order:

Figure 9: Trigger Execution order when both AFTER & INSTEAD OF Exists
Figure 9: Trigger Execution order when both AFTER & INSTEAD OF Exists

The DML statement that we want to execute enters SQL Server engine (Marked as 1). After making the execution plan, when the Table involved in DML statement has any triggers, the corresponding INSERTED, DELETED virtual table entries are made (Marked as 2). After making the virtual table entries the Instead of Trigger gets fired (Marked as 3). Note that the trigger is executed instead of actual DML statement so it can execute any DML Statement (Shown as 4). In our example, we are executing the delete statement again by using emp_id (The Actual delete is based on Employee last name) in where clause.  In our case, the Employee table Trigger, ‘After Delete’ is executed as the ‘Instead of’ trigger executes the DML statement (Shown as 5).

6. Trigger Handling Multi Row

First let us delete both the triggers on the employee table. Execute the below SQL statements to drop the triggers:

Drop Trigger Chief_NoDelete;
Drop Trigger Chief_Delete_instead;

To handle mutiple rows we should use cursor on the virtual tables. The new version Chief_NoDelete “Instead of TRIGGER” that handles multiple rows is shown in the below picture:

Figure 10: Trigger handling multiple rows affected
Figure 10: Trigger handling multiple rows affected

First the trigger is named and created on the Employee table for the Delete operation (Marked as 1).  After the trigger creation syntax, a “CURSOR” on the DELETED virtual table is declared and opened (Marked as 2). The cursor queries only Employee ID and Job Id from the ‘DELETED’ virtual table. Note that we are writing Trigger body for the Delete trigger & if it is an insert trigger then we should have used the ‘INSERTED’ virtual table. The fetch statement on the cursor retrieves columns from current row and assigns that into local variable. After fetch, the cursor pointer advances to next row in the cursor. The validity of the fetch on the current row is tested using “@@Fetch_Status” (marked as 3). The fetch will be successful when @@Fetch_Status contains 0. Based on the Fetch status we are forming iteration over the effected rows (DELETED Virtual table). Inside the iteration body, we are checking whether the current row belongs to ‘Chief Employee’ by testing the job id fetched from Cursor (Marked as 4).  Based on the test we are either discarding the deletion or allowing it. At the end of the iteration we are fetching the new row values into local variables (Marked as 5). Finally the cursor is closed and memory is cleared (Marked as 6).

Now Let us execute the delete statement, which affects more than one row. The delete statement is shown below:

Delete from Employee where job_lvl > 150;

Executing the above statement now produces the following result in the output window:

Figure 11: Result of Executing the Trigger
Figure 11: Result of Executing the Trigger

The trigger skips deleting the chief employee and its corresponding message is marked as 1 (Sample result). A successful deletion is marked as 2. Note that the InStead of trigger fires instead of the DML statement. So the trigger body should decide about the DML statement on which it is fired.  As we are experimenting the Delete record on the Employee table, below statement will be useful to restore the records:

insert employee values ('PTC11962M', 'Philip', 'T', 'Cramer', 2, 215, '9952', '11/11/89')
insert employee values ('AMD15433F', 'Ann', 'M', 'Devon', 3, 200, '9952', '07/16/91')
insert employee values ('F-C16315M', 'Francisco', '', 'Chang', 4, 227, '9952', '11/03/90')
insert employee values ('LAL21447M', 'Laurence', 'A', 'Lebihan', 5, 175, '0736', '06/03/90')
insert employee values ('PXH22250M', 'Paul', 'X', 'Henriot', 5, 159, '0877', '08/19/93')
insert employee values ('SKO22412M', 'Sven', 'K', 'Ottlieb', 5, 150, '1389', '04/05/91')
insert employee values ('RBM23061F', 'Rita', 'B', 'Muller', 5, 198, '1622', '10/09/93')
insert employee values ('MJP25939M', 'Maria', 'J', 'Pontes', 5, 246, '1756', '03/01/89')
insert employee values ('JYL26161F', 'Janine', 'Y', 'Labrune', 5, 172, '9901', '05/26/91')
insert employee values ('CFH28514M', 'Carlos', 'F', 'Hernadez', 5, 211, '9999', '04/21/89')
insert employee values ('VPA30890F', 'Victoria', 'P', 'Ashworth', 6, 140, '0877', '09/13/90')
insert employee values ('L-B31947F', 'Lesley', '', 'Brown', 7, 120, '0877', '02/13/91')
insert employee values ('ARD36773F', 'Anabela', 'R', 'Domingues', 8, 100, '0877', '01/27/93')
insert employee values ('M-R38834F', 'Martine', '', 'Rance', 9, 75, '0877', '02/05/92')
insert employee values ('PHF38899M', 'Peter', 'H', 'Franken', 10, 75, '0877', '05/17/92')
insert employee values ('DBT39435M', 'Daniel', 'B', 'Tonini', 11, 75, '0877', '01/01/90')
insert employee values ('H-B39728F', 'Helen', '', 'Bennett', 12, 35, '0877', '09/21/89')
insert employee values ('PMA42628M', 'Paolo', 'M', 'Accorti', 13, 35, '0877', '08/27/92')
insert employee values ('ENL44273F', 'Elizabeth', 'N', 'Lincoln', 14, 35, '0877', '07/24/90')
insert employee values ('MGK44605M', 'Matti', 'G', 'Karttunen', 6, 220, '0736', '05/01/94')
insert employee values ('PDI47470M', 'Palle', 'D', 'Ibsen', 7, 195, '0736', '05/09/93')
insert employee values ('MMS49649F', 'Mary', 'M', 'Saveley', 8, 175, '0736', '06/29/93')
insert employee values ('GHT50241M', 'Gary', 'H', 'Thomas', 9, 170, '0736', '08/09/88')
insert employee values ('MFS52347M', 'Martin', 'F', 'Sommer', 10, 165, '0736', '04/13/90')
insert employee values ('R-M53550M', 'Roland', '', 'Mendel', 11, 150, '0736', '09/05/91')
insert employee values ('HAS54740M', 'Howard', 'A', 'Snyder', 12, 100, '0736', '11/19/88')
insert employee values ('TPO55093M', 'Timothy', 'P', 'O''Rourke', 13, 100, '0736', '06/19/88')
insert employee values ('KFJ64308F', 'Karin', 'F', 'Josephs', 14, 100, '0736', '10/17/92')
insert employee values ('DWR65030M', 'Diego', 'W', 'Roel', 6, 192, '1389', '12/16/91')
insert employee values ('M-L67958F', 'Maria', '', 'Larsson', 7, 135, '1389', '03/27/92')
insert employee values ('PSP68661F', 'Paula', 'S', 'Parente', 8, 125, '1389', '01/19/94')
insert employee values ('MAS70474F', 'Margaret', 'A', 'Smith', 9, 78, '1389', '09/29/88')
insert employee values ('A-C71970F', 'Aria', '', 'Cruz', 10, 87, '1389', '10/26/91')
insert employee values ('MAP77183M', 'Miguel', 'A', 'Paolino', 11, 112, '1389', '12/07/92')
insert employee values ('Y-L77953M', 'Yoshi', '', 'Latimer', 12, 32, '1389', '06/11/89')
insert employee values ('CGS88322F', 'Carine', 'G', 'Schmitt', 13, 64, '1389', '07/07/92')
insert employee values ('PSA89086M', 'Pedro', 'S', 'Afonso', 14, 89, '1389', '12/24/90')
insert employee values ('A-R89858F', 'Annette', '', 'Roulet', 6, 152, '9999', '02/21/90')
insert employee values ('HAN90777M', 'Helvetius', 'A', 'Nagy', 7, 120, '9999', '03/19/93')
insert employee values ('M-P91209M', 'Manuel', '', 'Pereira', 8, 101, '9999', '01/09/89')
insert employee values ('KJJ92907F', 'Karla', 'J', 'Jablonski', 9, 170, '9999', '03/11/94')
insert employee values ('POK93028M', 'Pirkko', 'O', 'Koskitalo', 10, 80, '9999', '11/29/93')
insert employee values ('PCM98509F', 'Patricia', 'C', 'McKenna', 11, 150, '9999', '08/01/89')


-- 1. Simple DDL Trigger
--DDL trigger
   PRINT 'Contact Administrator to Drop view';

--2. Simple After Trigger
--Trigger Chief_NoDelete
--Action: After Deleting a Row
Create Trigger Chief_NoDelete
On Employee For Delete As
            Declare @JobID as smallint;
            Select @JobID = job_id from Deleted;
            if (@JobID = 2 or @JobID = 4)
                        Print 'Chief Employee Can not be Deleted';

--3. Simple Instead Of Trigger

--Trigger: Chief-Delete
--Action: Called In Place of Deleting a Row
Create Trigger Chief_Delete_instead
On Employee Instead of Delete As
            Declare @EmpID as char(9);
            Select @EmpID = emp_id from Deleted;
            Print 'Instead of Trigger Fired';
            Delete from Employee where emp_id = @EmpID;

--4. InStead Of Trigger dealing Batch Delete (Multiple Rows)

--Trigger: Chief_NoDelete
--Action: After Deleting a Row Process all the rows
Create Trigger Chief_NoDelete
On Employee Instead of Delete As
            -- Declaration of variable to hold data from DELETED
            -- virtual table
            Declare @emp_id char(9);
            Declare @job_id smallint;

            -- Declare cursor for the DELETED Table & Open it
            Declare Deleted_cursor CURSOR for
            Select emp_id, job_id from DELETED;
            Open Deleted_cursor;

            -- Attempt to fetch first record and loop next
            FETCH NEXT from Deleted_cursor into @emp_id, @job_id;
            While @@Fetch_Status = 0
                        if (@job_id = 2 or @job_id = 4)
                                                PRINT 'Chief Employee delete Ignored';
                                                PRINT '  Delete Skipped For ' + @emp_id;
                                                Delete from Employee where Emp_Id = @emp_id;
                                                PRINT 'Deleted Employee: ' + @emp_id;
            FETCH NEXT from Deleted_cursor into @emp_id, @job_id;
            CLOSE Deleted_cursor;
            DEALLOCATE Deleted_cursor;

Friday, May 01, 2015

[ C# ] - Spawning a process & get notification on Completion

1. Introduction

A "Program" is an executable stored in the physical disc. Say for example Notepad.exe stored in windows System32 folder is called as "Notepad Program". When the program in secondary storage (Disc) loads into primary storage for the execution of instructions set in it, we call it as "Process". Windows has the capability of spawning multiple processes at a time and we call windows as a Multi-Tasking operating system. When all processes running in the background they compete for the shared resources. OK, in this article we are going to see how we can spawn a process from a C# application.

2. About the Example

The below screenshot shows the application example:

The sample application has two parts. The first part starts a notepad process when the “Start” (Marked as 1) button is clicked.  You can enter the text document location in the textbox (Marked as 2) before clicking the start button. When a text document is specified, the notepad process opens that document. In the second part, a batch file is started as the process by clicking the start button (Marked as 7). The batch file actually performs a "DOS copy" operation, which requires Source and destination locations. These data can be supplied through two text boxes (Marked as 3, 4). When a batch file execution continues you can see a console window in the background. The checkbox “Make Silent” (marked as 5) suppresses the background command window. The checkbox “Notify when closed” (Marked as 6) when checked, allows the application receiving the notification when process finished its operation.

3. Starting the Notepad Process

1) To create a process, we have to use Process class, which can be found in the System.Diagnostics namespace. Below is the using statement for it:

//Sample 01: Use Diagnostics namespace
using System.Diagnostics;

2) The "Start() Method" of the "Process Class" is used to spawn a process. In our case, we supply the document name to the Process start method when the path is given in the Document Path text box. Note that path validation is not done in the code, so when you are testing, provide a valid text document path.

//Sample 03: Start Notepad process. 
private void btnStartNotepad_Click(object sender, EventArgs e)
    if (txtDocPath.Text == String.Empty)
        Process.Start("Notepad.exe", txtDocPath.Text);

4. About the Batch File

This demo application starts the “copy.bat” batch file as a Process. The content of the copy.bat is shown below:

The batch file actually copies one or more files or directories from the specified source to the specified destination. Source and destination location can be passed as parameters to the batch file and these parameters will be substituted to %1 and %2 in the batch file. Out C# example application, passes these parameters as arguments to the Process and we will see that in the next session.

5. Running .Bat as C-Sharp Process

The code explanation below describes the how a batch file can be run as a Process:

1) First, the "Process Instance" is declared as a private data member of the class. The code is given below:

//Sample 02: Process for the batch
private Process BatchProc = null;

2) When source and destination path text boxes are empty, then we will not start the process and return from the “Start” button click event procedure. Note that validation for the proper path is not done and hence while executing the sample application, the valid path should be entered into the text boxes. The code is given below:

//4.1: Return when the strings are empty. However, path validation not done
if (txtSrcPath.Text == string.Empty || txtDestPath.Text == string.Empty)

3) The "ProcessStartInfo Class" will be useful to set information required to start the process. In our example, we created ProcInfo object of type "ProcessStartInfo" and specified the Copy.bat batch file name through its FileName parameter. Since path name is not specified, the “Copy.bat” should reside in the same folder where executable exists. Next, the arguments (i.e.) Source and destination file location for the copy operation is populated into the ProcInfo object through its member Arguments.

//4.2: Populate Process Information
ProcessStartInfo ProcInfo = new ProcessStartInfo();
ProcInfo.FileName = "Copy.bat";
ProcInfo.Arguments = "\"" + txtSrcPath.Text + "\" " + "\"" + txtDestPath.Text + "\"";

While forming the Argument note the usage of escape sequence \” to include the double quote as the path may contain spaces. The Parameter1 and parameter2 are separated by a space while forming the string for Argument member of the ProcessStartInfo class. The below picture shows how the string concatenation applied:

The yellow highlighted double quotes are required as some path may contain spaces. In the above picture D:\Temp does not have any space but the path in argument 1 is having the space. As the inputs are taken from the UI, it is advisable to use the escape sequence while forming Argument string. Parameter1 and parameter2 are separated by a blank space.

4) Once ProcessStartInfo structure is ready, we can pass that structure to the "Start() method" of the Process class. This is shown below:

//4.4: Start the Process and Hook-up process Exit Event
BatchProc = Process.Start(ProcInfo);

At this stage, the process will start spawning the Dos console window and executes the Copy.bat. The net effect is, the copy is performed based on the parameter specified in the text boxes.

6. Process – Suppress as Background Runnable

Sometimes we need to start a process in silent mode. That means, the application responsible for starting the process should not display any background window and the process task should go silent. Setting the "Hidden WindowStyle" to the ProcessStartInfo class can do this.  The code for starting the process silent is given below:

//4.3: Start the process in silent Mode3
if (chkBoxSilent.Checked == true)
    ProcInfo.WindowStyle = ProcessWindowStyle.Hidden;

7. Process Exit Notification

In some cases, we need to get a notification from the process stating that the task is completed or process exited. In our case, the process exits when the task of copying the file from source to destination is completed. The application starting the process can handle the "Exited Event" of the process to get the notification from the process. The code given below:
if (chkWhenClosed.Checked == true)
    BatchProc.EnableRaisingEvents = true;
    BatchProc.Exited += new System.EventHandler(Batch_Done);

The handler for the above exited event is shown below which displays a message box stating copy operation is completed:

//Sample 05: Handle Batch completed event
private void Batch_Done(object sender, EventArgs e)
    MessageBox.Show("Copy Completed!");
    BatchProc.Exited -= new System.EventHandler(Batch_Done);
The Entire application can be watched here

Source Code: Download
Like this site? Tell it to your Firend :)