salkcid salkcid - 1 month ago 6
SQL Question

don`t understand how trigger works

Trigger should react to deleting row from table Suppliers if there is some supplies in the table Supplies from this supplier, trigger should cancel deleting.
This code allows to delete both, suppliers with supplies and suppliers without supply:

CREATE TRIGGER SuppliersDeleteCondition
ON Suppliers
FOR DELETE
AS
IF EXISTS (
SELECT 1
FROM Supplies Ses
JOIN DELETED D
ON D.SupplierID=Ses.SupplierID
)
BEGIN
RAISERROR ('This supplier has some supplies', 16, 1)
ROLLBACK TRANSACTION
END;


Approx tables view are:

Suppliers(SupplierID,Name,Address,BankDetails), Supplies(SupplyID,SupplierID,ProductID,ImplementationPeriod,Weight,Price)

Here is CREATE TABLE:

CREATE TABLE Suppliers
(
SupplierID INT IDENTITY,
Name VARCHAR(150) NOT NULL UNIQUE,
Address VARCHAR(900) NOT NULL,
BankDetails VARCHAR(9) NOT NULL UNIQUE,

CONSTRAINT pk_SupplierID PRIMARY KEY (SupplierID),
CONSTRAINT chk_Name_Suppliers CHECK (NOT Name LIKE '%[^a-z ]%' AND NOT Name LIKE '[ ]%' AND NOT Name LIKE '%[ ]' AND NOT Name LIKE '%[ ][ ]%'),
CONSTRAINT chk_Address_Suppliers CHECK (NOT Address LIKE '%[^a-zA-z0-9,./ ]%' AND NOT Address LIKE '[ ]%'),
CONSTRAINT chk_BankDetails_Suppliers CHECK (BankDetails LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
);
CREATE TABLE Supplies
(
SupplyID INT IDENTITY,
SupplierID INT,
ProductID INT,
ImplementationPeriod DATE NOT NULL,
Weight REAL NOT NULL,
Price MONEY NOT NULL,

CONSTRAINT pk_SupplyID PRIMARY KEY (SupplyID),
CONSTRAINT fk_SupplierID FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID) ON DELETE CASCADE,
CONSTRAINT fk_ProductID FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE,
CONSTRAINT chk_Weight_Supplies CHECK (Weight > 0)
);


When I delete the row like this:

DELETE FROM Suppliers WHERE SupplierID=18


this raw deleted and I got no error

Answer

You have a foreign key with the DELETE CASCADE option so the delete trigger is superfluous. The AFTER DELETE trigger will fire after rows from suppliers and supplies have been deleted so no rows will ever qualify for the EXISTS predicate. No need for the trigger here unless you could remove the foreign key, which I wouldn't recommend. Let SQL Server do the work for you.