/* Copy from here to to your Query Analyzer. */ /* These test codes contain examples of trigger procedures. */ /* Highlight and execute all the next drop statements simultaneously. Don't worry if there are error mesages. These are just to make sure there are no duplicate objects in the data base. */ drop trigger trgDeleteEmployee drop trigger trgInsertEmployee drop table TestEmployee drop trigger trgInsertRequest drop table TestPostRequest drop table TestPost /* Let's create a new test table containing data about positions in a company. Highlight and execute the next create tble command. */ create table TestPost ( cPostCode char (3) constraint pkcPostCode primary key, cPostDesc char (10) not null, iExisting int, iAvailable int ) /* Now, let's populate the new table with the following insert statements. Highlight all of them and execute them smultaneously. */ insert TestPost values ( '001', 'Clerk', 20, 50 ) insert TestPost values ( '005', 'Secretary', 10, 40 ) insert TestPost values ( '007', 'Programmer', 20, 60 ) insert TestPost values ( '009', 'Accountant', 10, 50 ) /* Let's check if the table had been appropriately populated. Highlight and execute the next command. */ select * from TestPost /* Now, let's create another test table that will contain requests for position. Highlight and execute the next create table command. */ create table TestPostRequest ( iRequestNo int identity (100,1), cPostCode char (3) foreign key references TestPost (cPostCode), iRequested int ) /* Next, let's create a trigger that would fire whenever a new record is to be added to the position request table. The rigger will not allow a request to be added to the position request table if the number requested is zero or more than the difference between the avalable and existing figures in the positions table. It will accept the new record if the number requested is less or equal than the net available figure. Highlight and execute the next create trigger command. */ create trigger trgInsertRequest on TestPostRequest for insert as declare @cPostCode char (3) declare @iRequested int declare @iExisting int declare @iAvailable int select @cPostCode = cPostCode, @iRequested = iRequested from Inserted select @iExisting = iExisting, @iAvailable = iAvailable from TestPost where cPostCode = @cPostCode if @iRequested <= 0 begin print "Number of Requested May Not Be Less Or Equal Zero" print "" print "Request Not Added" rollback transaction end else begin if @iRequested > (@iAvailable - @iExisting) begin print "Number Requested is More Than Available" print "" print "Request Not Added" rollback transaction end else begin select * from Inserted print "Above Record Added to Request Table" end end return /* Now, let's test the trigger. First, we check if there is any record in the position request table. Highlight and execute the next command. */ select * from TestPostRequest /* Now, let's insert an invalid row that requests 0 number of position items. Highlight and execute the next command. */ insert TestPostRequest values ( '007', 0 ) /* Did you see the error message? So, part of our procedure is working. Now, let's try to insert a record that has too many items requested. Highlight and execute the next command. */ insert TestPostRequest values ( '001', 100 ) /* It gave a different error message so we know that another apart of the procedure was working. Now, let's insert an acceptable position request record. Highlight and execute the next command. */ insert TestPostRequest values ( '001', 5 ) /* Wow, we had a positive response! Let's check if the record was really there in the table. Highlight and execute the next command. */ select * from TestPostRequest /* So, the record was really there. Now, let's create a new test table for our employee records. Highlight and execute the next command. */ create table TestEmployee ( cEmpId char (3) constraint pkcEmpId primary key, cPostCode char (3) constraint fkcPostCode foreign key references TestPost (cPostCode), vName varchar (20), mSalary money ) /* Next, let's populate our employee table with an initial set of data. Highlight and execute all the following commands together. */ insert TestEmployee values ( '010', '007', 'Oying Castro', 1000 ) insert TestEmployee values ( '012', '001', 'Wilhelm Lim', 3000 ) insert TestEmployee values ( '016', '005', 'Mika Escala', 10000 ) insert TestEmployee values ( '019', '009', 'Wilmer Tan', 11000 ) /* Okay, let's now check if the table was properly populated. Highlight and execute the next command. */ select * from TestEmployee /* Okay, so everything was properly inserted. Now, let's create a trigger that would fire when an emloyee record is inserted in the employee test table. The trigger will check first if there is an avaialable slot for the position in the position table and if no item is available for that position, the insert will be rejected. If an available slot is available, the trigger will add the record to the employee table and ensure that the corresponding figures on existing and available items for that position are properly adjusted. Highlight and execute the next create trigger command. */ create trigger trgInsertEmployee on TestEmployee for insert as declare @cEmpId char (3) declare @cPostCode char (3) declare @iExisting int declare @iAvailable int select @cPostCode = cPostcode from inserted select @iExisting = iExisting, @iAvailable = iAvailable from TestPost where cPostCode = @cPostCode if (@iAvailable - @iExisting) <= 0 begin print "No Available Slot for the Position" print "" select * from inserted print "Above Employee Record Not Added" rollback transaction end else begin update TestPost set iExisting = iExisting + 1, iAvailable = iAvailable -1 where cPostCode = @cPostCode select * from inserted print "Above Record Successfully Added" print "" select * from TestPost where cPostCode = @cPostCode print "Above is Status of Position After Update" end return /* So, okay, the procedure was created. Now, let's check first the contents of our employee test table. Highlight and execute the next command. */ select * from testEmployee /* Now, let's check our position test table so we will notice later if there will be any change. Highlight and execute the next command. */ select * from TestPost /* So we take note that we have thus far only four employees in our emloyee test table and we also take careful note that position code '009' has 40 available slots (50-10) in the test position table. Now, let's try to insert a valid employee record. Highlight and execute the next command. */ insert TestEmployee values ( '022', '009', 'Rowell Ruiz', 12000 ) /* Did you see the messages generated? First, it displayed the new record that was added. Then, it showed how the record for that particular position had been adjusted in our position test table. Wow, our trigger is working! Let' see if the record was actually added to the employee test table. Highlight and execute the next command. */ select * from testEmployee /* So we know that the new record has been added since now we have one employee more in the employee test table. Now, let's check if the position record was really adjusted in our position test table. Highlight and execute the next command. */ select * from TestPost /* Did you notice that position code '009' has now 11 (one more) items while it now only has 49 items (one less) available. Our trigger worked as expected! Now, let's insert a new record in our position test table that will include zero available items for the position (all positions filled up). Highlight and execute the next command. */ insert TestPost values ( '011', 'Director', 10, 10 ) /* Let's check if the position record has really been added. Highlight and execute the next command. */ select * from TestPost where cPostCode = '011' /* So the position record was really there in our position test table. Now, let's try to insert a new employee record that will contain the '011' position code for which we know there is no more available slot. Highlight and execute the next command. */ insert TestEmployee values ( '024', '011', 'Antonio Cruz', 15000 ) /* So the trigger did not allow the new employee record to be added to our employee test table. Let's check our employee test table to make sure it was not added. Highlight and execute the next command. */ select * from TestEmployee /* There is no employee with the same name as the record to be added in our table so the trigger worked. Now, let's create a new trigger that will fire when our employee test table is updated. The trigger will check first if the new salary is greater than the old one and if not, the record is rejected. If the new salary is greater than the old salary, the employee record is updated with the old and the mew record being shown to the user of the data base. Highlight and execute the next ceate trigger command. */ create trigger trgUpdateEmployee on TestEmployee for update as declare @mOldSalary money declare @mNewSalary money select @mOldSalary = mSalary from deleted select @mNewSalary = mSalary from inserted if @mNewSalary < @mOldSalary begin print "New Salary Cannot Be Less Than Old Salary" print "" select * from inserted print "Above Update Not Committed" rollback transaction end else begin print "Old Record Was:" print "" select * from deleted print "New Record Is:" print "" select * from inserted print "Update Committed" end return /* Okay, so the procedure was properly created. Now, let's see if it works. Highlight and execute the next command which updates the salary of employee Id '010' with a lower salary. Highlight and execute the next command. */ update TestEmployee set mSalary = 500 where cEmpId = '010' /* Did you see the error messages? Now, let's check if the employee record was updated in our employee test table. Highlight and execute the next command. */ select * from TestEmployee /* the record indeed was not updated! Our trigger is working! Now, let's try to make a valid update to the employee record. Higlight and execute the next command. */ update TestEmployee set mSalary = 2000 where cEmpId = '010' /* Did you see the success messages? So, our trigger is working. But let's also verify that the update was really done on the table. Highlight and execute the next command to display the contents of our employee test table. */ select * from TestEmployee /* So, the salary of employee id '010' was indeed increased from the previous 1,000 to the present 2,000. Our trigger worked! Now, let's create a trigger that will not alow any deletion of record in our employee test table. This trigger will check first if there is an employee with the given employee ID and if there is none it won't continue with the transaction. If the employee ID is valid, the trigger will display it together with the message that deletion is not allowed in the employee test table. Highlight and execute the next create trigger command. */ create trigger trgDeleteEmployee on TestEmployee for delete as declare @cEmpId char (3) select @cEmpId = cEmpId from deleted if @cEmpId is not null begin select * from deleted print "Deletion from Employee Table Not Allowed" print "" print "Above Record Not Deleted" end else print "No Employee With Given ID" rollback transaction return /* Now, let's check first the cntents of our employee test table. Highlight and execute the next command. */ select * from testEmployee /* First, let's delete an employee with non-existing employe ID. Highlight and execute the next command. */ delete from TestEmployee where cEmpId = '099' /* Did you see the error message? So, our trigger is working for invalid data. How about valid data? Let's try the next command which gives a valid employee ID. Highlight and execute it. */ delete from TestEmployee where cEmpId = '019' /* Did you see the messages? So, our trigger is working! Let's check if the record was really not removed from our employee test table. Highlight and execute the next command. */ select * from TestEmployee /* Indeed, the employee with ID of '019' was still recorded in our employee test table. Our trigger worked as expected! Please contact me if you still have more questions. Thanks.