CREATE TRIGGER trig_MyTrigger

ON someDatabase.dbo.Books

FOR INSERT, UPDATE

AS

DECLARE @authorID VARCHAR(5)
DECLARE @countAuthorsForThisBook NUMERIC

-- Get authorID from the virtual table "Inserted" 
SELECT @authorID = (SELECT AuthID FROM Inserted)

--Get authorID pre-existing in the someDatabase.dbo.Authors table
SELECT @countAuthorsForThisBook = (SELECT Count(AuthID) FROM someDatabase.dbo.Authors where AuthID=@authorID)

IF (@countAuthorsForThisBook < 1)
	BEGIN
		-- Rollback tran if authorID not found in the Authors table
		ROLLBACK TRAN
		RaisError('Error: Insert/Update failed! You cannot insert a book for an author that does not exist in our database!', 16, 1)
	END
ELSE
	print 'Insert/Update success!'
GO

CREATE TRIGGER trig_MyTrigger
    ON someDatabase.dbo.Books
FOR INSERT, UPDATE
AS
IF EXISTS
(
    SELECT A.AuthID
    FROM Inserted AS I
        JOIN dbo.Authors AS A
            ON A.AuthID = I.AuthID
    GROUP BY A.AuthID
    HAVING COUNT(*) < 1
)
BEGIN
    ROLLBACK ....

quote:

---

Originally posted by Kristen

You need to use Set Based processing to test all the rows in INSERTED - rather than the single row you are currently testing (which will be at random) using:

SELECT AuthID FROM Inserted

This may do the trick:

CREATE TRIGGER trig_MyTrigger
    ON someDatabase.dbo.Books
FOR INSERT, UPDATE
AS
IF EXISTS
(
    SELECT A.AuthID
    FROM Inserted AS I
        JOIN dbo.Authors AS A
            ON A.AuthID = I.AuthID
    GROUP BY A.AuthID
    HAVING COUNT(*) < 1
)
BEGIN
    ROLLBACK ....

but note that if any of the Books being updated satisfies the condition then all the other rows in the transaction will be rolled back too

---

quote:

---

Originally posted by Kristen

"I was thinking it over since my post and it looks like the best thing for me to do is to not do batch inserts/updates. It looks like rollback will rollback the entire remaining batch so I'm going to make each insert/update be one batch which will also meet the requirements of the system too."

Problem with that is that something COULD update/insert a batch - even if it is only you running some ad-hoc SQL to sort out some data!

And I think it is quite correct that if a single row violates a business-rule that the whole batch is aborted (its annoying for the operator, but it is correct from the database's perspective)

We tend to solve this type of thing by doing the actual INSERT / UPDATE in a Stored Procedure. That can take care of checking which records are in violation of business rules, and "deal with them" - e.g. move them to a "Failed" table. As a long-stop the Trigger would then enforce the business-rules too, so if there was an error in the programming of the Stored Procedure (or any other SQL) the Trigger would enforce it.

---