Null values INSERTED in trigger

Question

I want to copy content of one table to another table in the same database.

For this I wrote trigger on source table which triggered on AFTER INSERT UPDATE, there are 2 uniqueidentifier fields in the table which generates values based on newid() as default binding. Based on this uniqueidentifier I am checking whether the record is present on the destination table or not if present then it will update and if not present then insert dataset into the table.

Problem is when i insert a new record the INSERTED in trigger give me NULL values for the uniqueidentifier fields. In may case only one row is either update or insert so cursor is not used.

Below is my code, I am getting null values in @OriginalTable_MoveDataUID and @OriginalTable_ProcedureUID. Both the MoveDataUID and ProcedureUID are uniqueidentifier fileds.

Please share your thoughts or any alternative for this.

ALTER TRIGGER [dbo].[spec_ref_movedata_procedures_ToUpdate]
ON [dbo].[spec_ref_movedata_procedures]
AFTER INSERT, UPDATE
AS 
BEGIN

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION

DECLARE @OriginalTable_MoveDataUID NVarchar (100)
DECLARE @OriginalTable_ProcedureUID NVarchar (100)
DECLARE @PresentInHistoryYesNo int

SELECT @OriginalTable_MoveDataUID= MoveDataUID,@OriginalTable_ProcedureUID=ProcedureUID FROM INSERTED

-- inserted for checking purpose
INSERT INTO ERP_Test_NK_spec_ref_movedata_procedures_history_2 (MovedataUID,ProcedureUID) VALUES
(@OriginalTable_MoveDataUID,@OriginalTable_ProcedureUID)

SELECT @PresentInHistoryYesNo =  count(*) from spec_ref_movedata_procedures_history WHERE MoveDataUID=@OriginalTable_MoveDataUID AND ProcedureUID=@OriginalTable_ProcedureUID

IF @PresentInHistoryYesNo = 0
BEGIN
    -- insert opertions
    print 'insert record'
END
ELSE IF @PresentInHistoryYesNo = 1
BEGIN
    -- update opertions
    print 'update record'
END

COMMIT TRANSACTION
SET XACT_ABORT OFF

END

Your trigger has a MAJOR flaw in that is assumes that there will only be 1 row in inserted. This is not how triggers work in sql server. They fire once per operation, not once per row. You need to rewrite this into a set based operation. — Sean Lange
If this is within the same database, you do not need a DISTRIBUTED transaction. — StuartLC

Tab Alleman Tab Alleman · Accepted Answer · 2015-12-23T14:53:54

Instead of using variables, you could do this:

INSERT INTO ERP_Test_NK_spec_ref_movedata_procedures_history_2 (MovedataUID,ProcedureUID)
SELECT MoveDataUID,ProcedureUID FROM INSERTED

Null values INSERTED in trigger

1 Answers