Instead of trigger in SQL Server - looses SCOPE_IDENTITY?
- by kastermester
Hey StackOverflow,
I am (once again) having some issues with some SQL.
I have a table, on which I have created an INSTEAD OF trigger to enforce some buissness rules (rules not really important).
This works as intended. My issue is, that now when inserting data into this table, SCOPE_IDENTITY() now returns a NULL value, rather than the actual inserted identity, my guess is that this is because it is now out of scope - but then how do I get this in scope?
I am using SQL Server 2008.
Per request, here's the SQL:
Insert + Scope code
INSERT INTO [dbo].[Payment]([DateFrom], [DateTo], [CustomerId], [AdminId])
VALUES ('2009-01-20', '2009-01-31', 6, 1)
SELECT SCOPE_IDENTITY()
Trigger:
CREATE TRIGGER [dbo].[TR_Payments_Insert]
ON [dbo].[Payment]
INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF NOT EXISTS(SELECT 1 FROM dbo.Payment p
INNER JOIN Inserted i ON p.CustomerId = i.CustomerId
WHERE (i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo)
) AND NOT EXISTS (SELECT 1 FROM Inserted p
INNER JOIN Inserted i ON p.CustomerId = i.CustomerId
WHERE (i.DateFrom <> p.DateFrom AND i.DateTo <> p.DateTo) AND
((i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo))
)
BEGIN
INSERT INTO dbo.Payment (DateFrom, DateTo, CustomerId, AdminId)
SELECT DateFrom, DateTo, CustomerId, AdminId
FROM Inserted
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END
The code did work before the creation of this trigger, also I am using LINQ to SQL in C# and as far as I can see, I have no way of changing SCOPE_IDENTITY to @@IDENITY - is there really no way out of this one?
