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, , , .
