SQL Learning Summary (8)-Cursor and trigger technology

8.1 Creating Cursors

Syntax format:

DECLARE cursor name [insensitive] Cursor

[local | global]

[forward_only | scroll]

[Static | keyset | dynamic |fast_forward]

[Read_Only | scroll_locks | optimistic]

[Type_warning]

For SQL statements

[for [Read Only | update]]

8.2 reading data information from a cursor

Syntax format:

Fetch [Next | prior |first | last]

[Absolute n |relative N]

from cursor name [into variable name]

Note: The @ @fetch_status is a global variable that represents the status of the FETCH command returned:

return value	Significance
0	The FETCH command was executed successfully
-1	The FETCH command failed or the row data exceeded the range of the cursor data result set
-2	The data read is no longer present

Use Db_company

DECLARE DB_CURSOR5 scroll cursor

For

SELECT * from staff where sex is not null

Open Db_cursor5

Fetch first from Db_cursor5

FETCH NEXT from Db_cursor5

Fetch last from Db_cursor5

Fetch PRIOR from DB_CURSOR5

Fetch Absolute 2 from DB_CURSOR5

Fetch relative 2 from Db_cursor5

Close Db_cursor5

Deallocate Db_cursor5

8.3 using cursors to determine and output data

If @ @fetch_status =0

Begin

DECLARE @t varchar (10)

DECLARE @t1 varchar (10)

DECLARE @t2 int

Fetch first from DB_CURSOR6 to @t, @t1, @t2

Select @t as employee, @t1 as worker's name, @t2 as salary

End

Else

Begin

print ' does not exist for this record '

End

8.4 using cursors to modify and delete data

Use Db_company

DECLARE DB_CURSOR7 cursor Scroll

For

SELECT * FROM Employees

Open Db_curror7

Fetch first from DB_CURSOR7

Update employee Set wage = Salary +88 where CURRENT of DB_CURROR7

Fetch Absolute 4 from DB_CURSOR7

Delete from staff where current of DB_CURSOR7

Close Db_cursor7

Deallocate DB_CURSOR7

8.5 using cursors to display records in a database

Fetch first from DB_CURSOR8

While @ @fetch_status =0

Begin

FETCH NEXT from DB_CURROR8

End

8.6 using cursors in stored procedures

CREATE PROCEDURE Db_mypro

@x int

As

Begin

DECLARE mycursor scroll cursor

For

SELECT * FROM Employees

Open MyCursor

Fetch absolute @x from MyCursor

Close MyCursor

Deallocate mycursor

End

Db_mypro 2

8.7 View Cursor Properties

Use sp_cursor_list to view the current server's cursors and their properties, syntax format:

sp_cursor_list [@cursor_return =] cursor_variable_name output,[@cursor_scope =] Cursor_scope

Use sp_describe_cursor to view the cursor's global properties, syntax format:

sp_describe_cursor [@cursor_return =] output_cursor_variable output

{[, [@cursor_source =] N ' local '

, [@cursor_identity =] N ' local_cursor_name ']

| [, [@cursor_source =] N ' global '

, [, [@cursor_identity =] N ' global_cursor_name ']

| [, [@cursor_source =] N ' variable '

, [@cursor_identity =] N ' input_cursor_variable ']

}

8.8 Create a trigger

A trigger is a special type of stored procedure that is executed primarily through event triggering.

Ex post Trigger

Create trigger trigger name on table name [with encryption] for Insert[,update,delete] as

Begin

Command line or program block

End

Alternative triggers

Create trigger trigger name on table name or view name instead of insert [, Update,delete] As

Begin

Command line or program block

End

NOTE: Triggers can only relate to tables or views. To protect the code content of a code trigger, you can leverage the code content in the trigger with encryption encryption. After can only be defined in a table, it cannot be reached on a view. While instead of can be defined on the view.

Create Trigger Db_trigger4 on warehouse for update

As

Begin

Declare @old char (10)

Declare @new char (10)

Select @new = warehouse number from inserted

Select @old = warehouse number from deleted

Update staff set warehouse number [email protected] where warehouse number [email protected]

End

8.9 Modify, delete, and view triggers

8.9.1 modifying triggers

Change create to alter.

8.9.2 View trigger basic information

Execute sp_help db_trigger1 SELECT * from sys.triggers (view all)

8.9.3 To view data object information used by triggers

Execute sp_depends db_trigger1

8.9.4 View the trigger function code

Execute sp_helptext db_trigger1

8.9.5 Delete Trigger

Drop trigger {Triggername}[,n]

Triggername represents the name of the trigger to delete, and n indicates that multiple triggers can be deleted at the same time.

SQL Learning Summary (8)-Cursor and trigger technology