: deleted ( ) inserted ( ). , , , . SQL Server . , , . DDL, CREATE INDEX, .
DML inserted deleted .
- .
- .
- .
- .
deleted , DELETE UPDATE. DELETE UPDATE deleted. deleted .
inserted , INSERT UPDATE. inserted. inserted .
; deleted, inserted.
INSERT UPDATE, INSTEAD OF, , INSTEAD OF.
- timestamp.
- IDENTITY_INSERT ON, IDENTITY . IDENTITY_INSERT ON, INSERT .
- INSERT NOT NULL, DEFAULT.
- , , IDENTITY timestamp, , NULL - NOT NULL DEFAULT.
INSERT, UPDATE DELETE , INSTEAD OF, Database Engine , - . , inserted deleted, , , , inserted deleted, , .
|
|
|
inserted deleted INSTEAD OF, , SELECT, .
:
CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT e.EmployeeID, c.LastName, c.FirstName
FROM AdventureWorks.HumanResources.Employee e
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID
: int nvarchar. inserted deleted INSTEAD OF, , int EmployeeID, nvarchar LName nvarchar FName.
, - . , , . , inserted deleted . , .
, , (, @@ROWCOUNT).
DML , . CREATE TRIGGER, ; CREATE TRIGGER.
, TRUNCATE TABLE DELETE WHERE ( ), DELETE, TRUNCATE TABLE .
INSTEAD OF DELETE INSTEAD OF UPDATE , , DELETE UPDATE.
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH [ ENCRYPTION ] [ EXECUTE AS Clause ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
AS { sql_statement [; ] [,...n ] }
Trigger_name
.
table | view
, DML. INSTEAD OF.
WITH ENCRYPTION
CREATE TRIGGER . .
EXECUTE AS
, . , SQL Server , .
|
|
|
FOR | AFTER
AFTER , SQL, . , , , .
FOR, AFTER .
AFTER .
INSTEAD OF
, DML SQL, , .
INSERT, UPDATE DELETE INSTEAD OF.
{ [ DELETE ] [, ] [ INSERT ] [, ] [ UPDATE ] }
, DML, . . .
INSTEAD OF DELETE , ON DELETE. UPDATE , ON UPDATE.
Sql_statement
, Transact-SQL.
:
. PurchaseOrderHeader. , , , inserted Vendor . .
CREATE TRIGGER LowCredit ON PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
@vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p
INNER JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
RAISERROR (' .', 16, 1)
ROLLBACK TRANSACTION
END
GO
ALTER TRIGGER.
ALTER TRIGGER trigger_name
ON { table | view }
[ WITH [ ENCRYPTION ] [ EXECUTE AS Clause ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
AS { sql_statement [; ] [,...n ] }
ALTER TRIGGER CREATE TRIGGER.
CREATE TRIGGER (WITH ENCRYPTION), , , WITH ENCRYPTION.
, :
ALTER TRIGGER LowCredit ON PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
@vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p
INNER JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.VendorID = i.VendorID
IF @creditrating = 10
BEGIN
RAISERROR (' .', 16, 1)
ROLLBACK TRANSACTION
END
GO
DROP TRIGGER.
DROP TRIGGER trigger_name
Trigger_name
.
, :
DROP TRIGGER LowCredit
|
|
|
16
?
. , . SELECT. SELECT , .
, . , . , .
, . . , . , , , , .
; .
, .
Database Engine , , . , . .
, Database Engine . , , . , , .
