1-mavzu. Ma’lumotlar bazasi obyektlarini yaratish

Triggerni to’xtatib qo’yish
DISABLE TRIGGER Products_INSERT_UPDATE ON Products


Triggerni yoqib qo’yish
ENABLE TRIGGER Products_INSERT_UPDATE ON Products

Trigger INSERT, UPDATE, DELETE operasiyalari

Trigger operasiyalari bilan ishlash uchun quyidagi productsdb ma`lumotlar bazasini aniqlaymiz:
CREATE DATABASE productsdb;
GO
USE productsdb;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
CREATE TABLE History
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL,
Operation NVARCHAR(200) NOT NULL,
CreateAt DATETIME NOT NULL DEFAULT GETDATE(),
);

Bu yerda ikkita jadval mavjud: Products- tovarlarni saqlash uchun va History- tovar bilan operasiyalar tarixini saqlash uchun.

Ma`lumotlarni qo'shish
Triggerga ma`lumotlarni qo'shishda (INSERT buyrug'ini bajarishda), biz qo'shilgan ma`lumotlarni INSERTED virtual jadvalidan olishimiz mumkin.
Qo'shib qo'ygandan so'ng ishga tushadigan triggerni aniqlaylik:
USE productsdb
GO
CREATE TRIGGER Products_INSERT
ON Products
AFTER INSERT
AS
INSERT INTO History (ProductId, Operation)
SELECT Id, 'Dobavlen tovar ' + ProductName + ' firma ' + Manufacturer
FROM INSERTED

Ushbu trigger INSERTED virtual jadvalidagi mahsulot ma`lumotlarini History jadvaliga qo'shib beradi.

Ma`lumotlarni Products jadvaliga qo'shamiz va History jadvalidan ma`lumot olamiz:
USE productsdb;
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES('iPhone X', 'Apple', 2, 79900)
SELECT * FROM History


Ma`lumotlarni o’chirish
O'chirishda barcha o'chirilgan ma`lumotlar DELETED virtual jadvaliga joylashtiriladi:
USE productsdb
GO
CREATE TRIGGER Products_DELETE
ON Products
AFTER DELETE
AS
INSERT INTO History (ProductId, Operation)
SELECT Id, 'Udalen tovar ' + ProductName + ' firma ' + Manufacturer
FROM DELETED

Bu yerda, avvalgi trigger bilan bo'lgani kabi, biz o'chirilgan mahsulotlar haqidagi ma`lumotlarni History jadvaliga joylashtirdik.

O'chirish buyrug'ini bajaramiz:
USE productsdb;
DELETE FROM Products
WHERE Id=2
SELECT * FROM History