.


:




:

































 

 

 

 


. . (). . .




1. DECLARE DECLARE SET.

2. (OPEN).

3. (FETCH).

4. (CLOSE).

5. . (DEALLOCATE). CLOSE.

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL] -

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR < SELECT>

[FOR UPDATE [OF __]] ,

OPEN , ( SELECT). OPEN {[GLOBAL] < > | @cursor_variable}

FETCH :

FETCH[ [NEXT | PRIOR | FIRST | LAST |

ABSOLUTE { | @var} |

RELATIVE { | @var} ] FROM]

{[GLOBAL] < > | @cursor_variable}

[INTO @var1, @var2, ] ,

CLOSE , .CLOSE {[GLOBAL] < > | @cursor_variable}

DEALLOCATE . , , . CLOSE. DEALLOCATE {[GLOBAL] < > | @cursor_variable}

1. @@CURSOR_ROWS , . :

. , . KEYSET STATIC .

-1. (DYNAMIC). .. , .

0. , .

. , .

2. CURSOR_STATUS({{local | global}, | variable, @cursor_var}) . 2 : 1- local global , (2- ) variable (2- ). :

1. , 1 ( DYNAMIC 0 ).

0. , . DYNAMIC .

-1. .

-2. , . , .

-3. , .

3. @@FETCH_STATUS FETCH ( ):

0. FETCH .

-1. FETCH .

-2. .

13. . . / DML (). (). , ( COLUMNSUPDATED UPDATE). inserted deleted. .

, , . () , - () FOREIGN KEY ( ), , , , - . : DML, DDL LOGON. DML ( ) INSERT, UPDATE DELETE . MS SQL Server 2000. DDL CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS . LOGON MS SQL Server. DDL LOGON MS SQL Server 2005

CREATE TRIGGER [_.]_

ON {_ | _}

[WITH

{ ENCRYPTION |

EXEC[UTE] AS { CALLER | SELF | OWNER | _} }[,]]

{ FOR [AFTER] | INSTEAD OF}

{[INSERT] [,] [UPDATE] [,] [DELETE]}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

{ __SQL |

EXTERNAL NAME _._._ [;]}

DML COLUMNS_UPDATED() UPDATE(_), inserted deleted. , , . INSTEAD OF , . inserted deleted , .

COLUMNS_UPDATED() varbinary, , 8- . 1- 1- 8- .. () 1- , 1- 2- .. - , 1, 0. COLUMNS_UPDATED() , UPDATE . INSERT COLUMNS_UPDATED() , .

UPDATE(_) COLUMNS_UPDATED(), : TRUE, , FALSE . IF .

inserted deleted , DML . , , DML . deleted , , UPDATE, . inserted ( INSERT) , . , INSTEAD OF / inserted, deleted. - , , ( ) () , . inserted deleted 0.

@order , varchar(10) : First , Last , None . , - .

@stmttype , , varchar(50) : INSERT, UPDATE, DELETE, LOGON , DDL .

@namespace DDL : DATABASE SERVER. LOGON SERVER, DML , NULL.

, , First Last ALTER TRIGGER, sp_settriggerorder.


. . DML . , . , DML.

, , . () , - () FOREIGN KEY ( ), , , , - . : DML, DDL LOGON. DML ( ) INSERT, UPDATE DELETE . MS SQL Server 2000. DDL CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS . LOGON MS SQL Server. DDL LOGON MS SQL Server 2005

AFTER , DELETE , c1 1, . , , .

, . sysobjects, , trig1, TR (). uid ( ) . sys.schemas schema_id , name .

if exists(select * from sysobjects where name = 'trig1' and

uid = (select schema_id from sys.schemas where name = 'dbo') and type = 'TR')

Drop trigger trig1 go

DML COLUMNS_UPDATED() UPDATE(_), inserted deleted. , , . INSTEAD OF , . inserted deleted , .

, , :

1. COLUMNS_UPDATED() 0, (DELETE), deleted .

2. COLUMNS_UPDATED() 0, INSERT, UPDATE.

2.1. inserted - , deleted , INSERT.

2.2. inserted deleted inserted deleted COLUMNS_UPDATED() , , - , UPDATE.

2.3. inserted deleted COLUMNS_UPDATED() , , INSERT UPDATE , .. UPDATE , INSERT .

15. . . / DDL (). (). , ( EVENTDATA). .

, , . () , - () FOREIGN KEY ( ), , , , - . : DML, DDL LOGON. DML ( ) INSERT, UPDATE DELETE . MS SQL Server 2000. DDL CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS . LOGON MS SQL Server. DDL LOGON MS SQL Server 2005

DML :

DROP TRIGGER _._1[, _._2] [;]

DDL :

DROP TRIGGER _1[, _2]

ON {DATABASE | ALL SERVER}

[;]

LOGON :

DROP TRIGGER _1[, _2]

ON ALL SERVER

DATABASE , DDL () , ALL SERVER , DDL () .

, :

1. DML .

2. , sys.objects, sys.triggers, sys.sql_modules .

3. DDL DROP TRIGGER , .

, DROP TRIGGER CREATE TRIGGER


16. . . / LOGON (). (). .

, , . () , - () FOREIGN KEY ( ), , , , - . : DML, DDL LOGON. DML ( ) INSERT, UPDATE DELETE . MS SQL Server 2000. DDL CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS . LOGON MS SQL Server. DDL LOGON MS SQL Server 2005

:

LOGON :

CREATE TRIGGER _

ON ALL SERVER

[WITH

{ ENCRYPTION |

EXEC[UTE] AS { CALLER | SELF login_name}

}[,]

]

{ FOR [AFTER]} LOGON

AS

{

__SQL |

EXTERNAL NAME _._._ [;]

}

:

DML :

DROP TRIGGER _._1[, _._2] [;]

DDL :

DROP TRIGGER _1[, _2]

ON {DATABASE | ALL SERVER}

[;]

LOGON :

DROP TRIGGER _1[, _2]

ON ALL SERVER

DATABASE , DDL () , ALL SERVER , DDL () .

, :

4. DML .

5. , sys.objects, sys.triggers, sys.sql_modules .

6. DDL DROP TRIGGER , .

, DROP TRIGGER CREATE TRIGGER


17. . . / ( ENABLE/ DISABLE ALTERTABLE, / ). . . .

, , . () , - () FOREIGN KEY ( ), , , , - . : DML, DDL LOGON. DML ( ) INSERT, UPDATE DELETE . MS SQL Server 2000. DDL CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS . LOGON MS SQL Server. DDL LOGON MS SQL Server 2005

| .

 

{ENABLE | DISABLE} TRIGGER

{[_.]_1[,[_.]_1] | ALL}

ON {_ | DATABASE | ALL SERVER} [;]

 

:

1. _ DML .

2. ALL , ( ON) .

3. ON _|_ DML , DATABASE DDL , ALL SERVER DDL LOGON .

4. DISABLE TRIGGER, , , , ( EXECUTE sp_executesql). , {ENABLE | DISABLE} TRIGGER, ( begin go).

 

| DML ALTER TABLE, :

 

ALTER TABLE [ _.[_]. | _.] _

{ENABLE | DISABLE } TRIGGER

{ALL | _1[, _2]

 

, , . | , , ALL.

ALTER TABLE _ DISABLE TRIGGER {_ | ALL} ALTER TABLE _ ENABLE TRIGGER {_ | ALL} , . ALTER TABLE _ DISABLE TRIGGER {_ | ALL} , DML , , , ( EXECUTE sp_executesql).


18. . . / , ( ). . . .

, ( ) . , . , .

, .

, , .

, , .

CREATE VIEW [ schema_name. ] view_name [ (column [,...n ]) ]

[ WITH <view_attribute> [,...n ] ]

AS select_statement

[ WITH CHECK OPTION ] [; ]

<view_attribute>::=

{ [ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

, :

, UPDATE, INSERT DELETE, .

. - , :

: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR VARP;

. , . , UNION, UNION ALL, CROSSJOIN, EXCEPT INTERSECT, .

GROUP BY, HAVING DISTINCT .

TOP select_statement WITH CHECK OPTION.

FROM, . , Database Engine . . .

, .

INSTEAD OF

, INSTEAD OF. INSTEAD OF , . , .

, . Database Engine .


19. . . / ( ). , . . (). .

Transact-SQL , . Transact-SQL .

sp_executesql [ @statement = ] statement[ {, [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][,...n ]' } {, [ @param1 = ] 'value1' [,...n ] }]

[ @statement = ] statement - , Transact-SQL.

[ @params = ] N'@parameter_namedata_type [,... n ] ' - , , stmt. . . n , . , statement, @params. Transact-SQL stmt , @params . NULL.

[ @param1 = ] 'value1' - , . . , stmt, . Transact-SQL stmt , .

[ OUT | OUTPUT ] - , . text, ntext image , CLR. OUTPUT , CLR.

n - . . , , .

 

SELECT, @level.

EXECUTE sp_executesql N'SELECT * FROM AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @level', N'@level tinyint', @level = 109;

, sp_executesql EXECUTE. Transact-SQL stmt sp_executesql sp_executesql. stmt , , sp_executesql. , sp_executesql, , , sp_executesql. sp_executesql , sp_executesql. sp_executesql.

sp_executesql Transact-SQL, . Transact-SQL , SQL Server, , , .





:


: 2016-07-29; !; : 491 |


:

:

, ; , .
==> ...

1004 - | 822 -


© 2015-2024 lektsii.org - -

: 0.101 .