CODEWITHSUNDEEP
sqlsql-servertriggers
Mohamad Chaker
Mohamad Chaker

Reputation: 11

How do I make the "Inserted" return one value per insertion into the table?

USE CIS111_BookStoreMC
GO

IF OBJECT_ID('AssetsMC_INSERT_UPDATE') IS NOT NULL
    DROP TRIGGER AssetsMC_INSERT_UPDATE
GO

CREATE TRIGGER AssetsMC_INSERT_UPDATE
    ON Assets
    AFTER INSERT,UPDATE
AS
BEGIN

    IF (((SELECT Description FROM Assets WHERE Description IN (SELECT Description FROM Inserted)) = 'Desk') AND
        ((SELECT Cost FROM Assets WHERE Cost IN (SELECT Cost FROM Inserted)) > 200.00))
 
        
    BEGIN
            RAISERROR('Desk cost should be less than $200.00', 11, 1)
            ROLLBACK TRAN   
            RETURN;     
    END
END

I do realize that "Inserted" is returning more than one value because I am inserting multiple values into the assets table as such

USE CIS111_BookStoreMC
INSERT INTO Assets
    (Description, Cost, PurchaseDate, Category, LocationID)
VALUES
    ('Laptop', 925.99, '2019-01-11', 'Computers', 1),
    ('Laptop', 925.99, '2019-01-11', 'Computers', 2),
    ('Laptop', 925.99, '2019-01-11', 'Computers', 1),
    ('Laptop', 925.99, '2019-01-11', 'Computers', 2),
    ('Server', 6000.00, '2019-01-11', 'Computers', 1),
    ('Server', 6000.00, '2019-01-11', 'Computers', 2),
    ('Desk', 199.99, '2019-01-16', 'Furniture', 1),
    ('Desk', 350.00, '2019-01-16', 'Furniture', 1),
    ('Desk', 199.00, '2019-01-16', 'Furniture', 2),
    ('Desk', 170.00, '2019-01-16', 'Furniture', 2),
    ('File Cabinet', 1200.00, '2019-01-16', 'Furniture', 1),
    ('File Cabinet', 1200.00, '2019-01-16', 'Furniture', 2),
    ('Water Cooler', 550.00, '2019-01-16', 'Miscellaneous', 2),
    ('Water Cooler', 560.00, '2019-01-16', 'Miscellaneous', 1)

How would I go about implementing this trigger to run for each insertion into the table?

Upvotes: 1

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You wouldn't use a trigger for this. You would use a check constraint:

alter table assets add constraint chk_desk_price
    check (not (description = 'Desk' and price > 200.0) );

Upvotes: 1

Stu
Stu

Reputation: 32609

Another approach you could consider would be to use an instead of trigger. This gives you the ability to validate the insert or update completely since it does not happen at all, instead your trigger runs and you can choose how to proceed.

For this kind of use-case I prefer to keep the insert and update operations separate with a trigger for each.

The bare-bones idea is to actually insert or update in the trigger only the qualifying rows, and then act on the failing rows eg raise an error - or more normally - just log those row(s) to a separate table for reporting etc.

You would likely want to implement a rules table and join to this to determine which rows will fail or pass.

Your triggers would look something like the following

create trigger AssetsMC_INSERT
on Assets
instead of insert
as

insert into Assets (Description, Cost, PurchaseDate, Category, LocationID)
select Description, Cost, PurchaseDate, Category, LocationID
from inserted
where not exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)

if exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)
begin
    raiserror('Desk cost should be less than $200.00', 0, 1)     
end

go

create trigger AssetsMC_UNPDATE
on Assets
instead of update
as

update a set
    Description = i.Description,
    Cost = i.Cost,
    PurchaseDate = i.PurchaseDate,
    Category = i.Category,
    LocationID = i.LocationId
from inserted i join Assets a on a.Id=i.Id
where not exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)

if exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)
begin
    raiserror('Desk cost should be less than $200.00', 0, 1)     
end

go

Upvotes: 1

Related Questions