Database System SSD7 Experiment 4 Trigger and Cursor

Experiment 4 Triggers and Cursors

I. Experimental Purpose

_Familiar with the method of database integrity control by SQL statement, understand the concept, definition method and trigger condition of trigger. Understand how cursors are defined, opened, used, closed and released.

II. EXPERIMENTAL CONTENTS

_For experiment 3, a sales database was built.

  1. Set up a trigger that allows only "dbo" users to delete data in the employee table, otherwise an error occurs.
  2. Write a DELETE trigger for the employee table.
  3. Write an UPDATE trigger for the employee table.
  4. The average salary of employees in the employee table is counted, and the number of employees whose salaries are lower than the average is output, as well as their names and salaries (using cursors).
III. EXPERIMENTAL METHODS

_This experiment mainly uses SQL Server database management tools and sales database built in Experiment 3 to set triggers and use cursors.
_The experimental scheme is mainly carried out in combination with PPT and web-based courses.

IV. EXPERIMENTAL STEPS
  1. Set up a trigger that allows only "dbo" users to delete data in the employee table, otherwise an error occurs.
  2. Write a DELETE trigger for the employee table.
  3. Write an UPDATE trigger for the employee table.
  4. The average salary of employees in the employee table is counted, and the number of employees whose salaries are lower than the average is output, as well as their names and salaries (using cursors).
V. EXPERIMENTAL RESULTS

1. Set up a trigger that allows only "dbo" users to delete data in the employee table, otherwise an error will occur.

CREATE TRIGGER dboDelete
ON employee
FOR DELETE
AS
	IF exists (SELECT * from deleted)
		begin
			if USER != 'dbo'
			begin
				print 'only dbo Users have deletion rights!'
				rollback
			end
		end

DELETE FROM employee WHERE emp_no='E0019'
SELECT * FROM employee WHERE emp_no='E0019'

When the user is "dbo", the deletion is successful:

2. Write a DELETE trigger for the employee table.

CREATE TRIGGER employeeDelete
ON employee
FOR DELETE
AS
	print('Executing employee Table deletion operation.')

DELETE FROM employee WHERE emp_no='E0019'

3. Write an UPDATE trigger for the employee table.

CREATE TRIGGER employeeUpdate
ON employee
FOR UPDATE
AS
	print('Executing employee Table update operation.')

UPDATE employee SET emp_name='Wang Renhua' WHERE emp_no='E0019'

4. Statistics the average salary of employees in the employee table, the number of employees who output less than the average salary, and their names and salaries (using cursors).

DECLARE @name varchar(10),
		@salary varchar(10)
--1.declare cursor
DECLARE lowAvgSalaryEmployee CURSOR
FOR
	SELECT emp_name, salary
	FROM employee
	WHERE salary<(SELECT avg(salary) FROM employee)
--2.open
OPEN lowAvgSalaryEmployee
--3.Promoting cursors
print('name  salary')
FETCH NEXT
FROM lowAvgSalaryEmployee
INTO @name,@salary
--4.Line-by-line operation
while(@@fetch_status=0)
    begin
		print(@name+'  '+@salary)
        FETCH NEXT FROM lowAvgSalaryEmployee INTO @name,@salary
    end
--5.Close the cursor
CLOSE lowAvgSalaryEmployee
--6.Release cursor
DEALLOCATE lowAvgSalaryEmployee

VI. EXPERIMENTAL CONCLUSION

_The experimental data and results are shown in point 5. The obtained results are in line with expectations, and there are no errors.
Conclusion: Triggers can be used to enforce business rules and data integrity in databases, and to achieve complex reference integrity and data consistency that can not be guaranteed by primary and foreign keys.
_Using cursors, the results of queries can be operated line by line, instead of the whole relational table.

VII. Summary of Experiments

_This experiment mainly learned how to use triggers to further constrain the integrity of data in the database, and to understand triggers more deeply. At the same time, we learned to use cursor operation to query the results line by line, and use cursor six steps, variable type and other details to deal with.
_encountered

Solution: After thinking about the attempt, we found that the value type obtained by the cursor has been converted to char by default, so we set the variable type of salary to varchar.

Tags: Database SQL less

Posted on Sun, 04 Aug 2019 05:08:35 -0700 by jkatcher