CREATE TRIGGER dbo.FIM_AGILR_AFTER_UPDATE
AFTER UPDATE ON FIM_AGILE
AS
	IF NOT EXISTS 
	( 
		SELECT * FROM FIM_AGILE_CHANGES f
		INNER JOIN INSERTED i ON i.internal_id = f.internal_id
	)
	INSERT INTO FIM_AGILE_CHANGES
	SELECT
		INSERTED.INTERNAL_ID,
		'Modify'
	FROM
		INSERTED;
GO

quote:

---

Originally posted by James K

Translation to T-SQL would be something like shown below. However, either I am not understanding the logic in the Oracle code (which is very likely), or the logic is not quite right. In any case, here is my attempt:

CREATE TRIGGER dbo.FIM_AGILR_AFTER_UPDATE
AFTER UPDATE ON FIM_AGILE
AS
	IF NOT EXISTS 
	( 
		SELECT * FROM FIM_AGILE_CHANGES f
		INNER JOIN INSERTED i ON i.internal_id = f.internal_id
	)
	INSERT INTO FIM_AGILE_CHANGES
	SELECT
		INSERTED.INTERNAL_ID,
		'Modify'
	FROM
		INSERTED;
GO

The concern I have relates to the fact that a single update can affect many rows, and some of the updated rows may be in FIM_AGILE_CHANGES and some may not be. In that case, the logic as I understood, and as I have translated it will not insert any rows into the FIM_AGILE_CHANGES table.

---

CREATE TRIGGER dbo.FIM_AGILR_AFTER_UPDATE
AFTER UPDATE ON FIM_AGILE
AFTER UPDATE
AS
	INSERT INTO FIM_AGILE_CHANGES
	SELECT
		INSERTED.INTERNAL_ID,
		'Modify'
	FROM
		INSERTED i
       WHERE NOT EXISTS (SELECT 1 FROM FIM_AGILE_CHANGES 
                         WHERE  internal_id = i.internal_id)
GO