1-mavzu. Ma’lumotlar bazasi obyektlarini yaratish

Ma`lumotlarni o`zgartirish
Ma`lumotlarni yangilash tiggeri UPDATE operasiyasi amalga oshirilganda ishga tushadi. Bunday triggerda biz ikkita virtual jadvaldan foydalanishimiz mumkin. INSERTED jadvali qator qiymatlarini yangilanishdan keying variantini saqlaydi va DELETED jadvali qatorlarning dastlabki(ya’ni yangilanmasdan oldingi) qiymatlarini saqlaydi.
O`zgartirish triggerini yarataylik:
USE productsdb
GO
CREATE TRIGGER Products_UPDATE
ON Products
AFTER UPDATE
AS
INSERT INTO History (ProductId, Operation)
SELECT Id, 'Obnovlen tovar ' + ProductName + ' firma ' + Manufacturer
FROM INSERTED

Ma`lumotlar yangilanganida, bu trigger ishlaydi:

INSTEAD OF triggeri
INSTEAD OF triggeri ma`lumotlar bilan ishlash amallari o'rniga ishga tushadi. U printsipial ravishda AFTER triggri bilan bir xil aniqlanadi, farqi uni faqat bitta operasiya(INSERT, DELETE yoki UPDATE) uchun belgilash mumkin. U jadvalga ham, ko`rinishga ham qo`llanilishi mumkin (AFTER trigger faqat jadvallarga tegishli edi).
Masalan, quyidagi ma`lumotlar bazasini va jadvalini yarataylik:
CREATE DATABASE prods;
GO
USE prods;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
Price MONEY NOT NULL,
IsDeleted BIT NULL
);

Bu yerda jadvalda yozuv o'chirilganligini ko`rsatuvchi IsDeleted ustuni mavjud. Ya'ni, ma`lumotlar bazasidan butunlay o'chirish o'rniga, biz IsDeleted qiymatini o`zgartiramiz, unda yozuv ma`lumotlar bazasida qoladi.

Yozuvni o'chirish uchun triggerni aniqlaymiz:
USE prods
GO
CREATE TRIGGER products_delete
ON Products
INSTEAD OF DELETE
AS
UPDATE Products
SET IsDeleted = 1
WHERE ID =(SELECT Id FROM deleted)

Jadvalga ba'zi ma`lumotlarni qo'shib, keyin undan o’chirib tashlaymiz:

USE prods;
INSERT INTO Products(ProductName, Manufacturer, Price)
VALUES ('iPhone X', 'Apple', 79000),
('Pixel 2', 'Google', 60000);
DELETE FROM Products
WHERE ProductName='Pixel 2';
SELECT * FROM Products;

Natija:

DDL triggerlar
Database Engine komponenti CREATE DATABASE, DROP TABLE va ALTER TABLE kabi DDL-instruksiyalar uchun triggerlarni aniqlashga imkon beradi. DDL operatorlar uchun triggerlar quyidagi sintaksisga ega:
CREATE TRIGGER [schema_name.]trigger_name
ON {ALL SERVER | DATABASE }
[WITH {ENCRYPTION | EXECUTE AS clause_name]
{FOR | AFTER } { event_group | event_type | LOGON}
AS {batch | EXTERNAL NAME method_name}