Errors And ROLLBACKs In Triggers

Hello,

I'm using SQL Server 2005 Express. I have this task I've been trying to solve:





Code BlockCREATE TABLE A


(
a INT UNIQUE
)
What I want to do is to create an AFTER INSERT trigger on A that inserts 2*a and 3*a for each inserted a. As the a column is UNIQUE, errors might occur. The trigger is to a) ROLLBACK the whole transaction b) if the error occurs when inserting a, then ROLLBACK insertion of 2*a and 3*a. The trigger is suppsed not to check whether 2*a or 3*a are in A already.

The first one seems easy:





Code Block

CREATE TRIGGER InsTr ON A
AFTER INSERT
AS
BEGIN
BEGIN TRY
INSERT INTO A SELECT 2 * a FROM inserted
INSERT INTO A SELECT 3 * a FROM inserted
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO

DELETE FROM A
GO

BEGIN TRAN
INSERT INTO A VALUES (4) -- insert 4, 8, 12
INSERT INTO A VALUES (2) -- insert 2, 4, 6
INSERT INTO A VALUES (1) -- insert 1, 2, 3
COMMIT
GO

But I can't seem to get the second right:





Code Block

DROP TRIGGER InsTr
GO

CREATE TRIGGER InsTr ON A
AFTER INSERT
AS
BEGIN
SAVE TRANSACTION Wst
BEGIN TRY
INSERT INTO A SELECT 2 * a FROM inserted
INSERT INTO A SELECT 3 * a FROM inserted
COMMIT TRANSACTION Wst
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION Wst
END CATCH
END
GO





DELETE FROM A
GO




BEGIN TRAN
INSERT INTO A VALUES (4) -- insert 4, 8, 12
INSERT INTO A VALUES (2) -- insert 2, 4, 6
INSERT INTO A VALUES (1) -- insert 1, 2, 3
COMMIT
GO


The result is that the A is missing values 1, 2. How to solve this?