I have a database and there are two tables Ads
and Images
. There is a primary key adid
in Ads
table which is a foreign key in Images
table.
I want to created a constraint on table Images
that not more than 5 adid
can be stored in Images
table.
I need to know what this type of constraint is called and how I can accomplish this with query in SQL Server.
There is no constaint to enforce that rule, but a trigger like the following can do it:
CREATE TRIGGER Images_not_more_than_five_per_add
ON Images FOR INSERT
AS
DECLARE @RowCount int
SET @RowCount = @@ROWCOUNT
SET NOCOUNT ON
IF @RowCount = 1
BEGIN
IF (SELECT COUNT(*) FROM Images WHERE Images.addid = (SELECT addid FROM inserted)) > 5
BEGIN
RAISERROR('No more than five images per add are allowed', 16, -1)
ROLLBACK
RETURN
END
END
ELSE
BEGIN
IF EXISTS (
SELECT *
FROM
Images
INNER JOIN (
SELECT DISTINCT addid FROM inserted
) I ON Images.addid = I.addid
GROUP BY
Images.addid
HAVING COUNT(*) > 5
)
BEGIN
RAISERROR('No more than five images per add are allowed', 16, -1)
ROLLBACK
RETURN
END
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With