TRIGGER Syntax

Question

Looking to create a TRIGGER on INSERT of a New Row. Is this correct? Do I need to qualify it? IntelliSense is kind of asking for a WHERE Clause...

CREATE TRIGGER [TR_rsm_emailnotification_INSERT]
ON [dbo].[rsm_emailnotification]
AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON
		;
		UPDATE		[dbo].[rsm_emailnotification]
		SET			[SinkCreatedOn] = GETDATE (),
					[SinkModifiedOn] = GETDATE (),
					[rsm_notificationtype_entitytype] = 'rsm_status',
					[rsm_program_entitytype] = 'po_program',
					[regardingobjectid_entitytype] = 'rsm_enrollment',
					[createdby_entitytype] = 'systemuser',
					[modifiedby_entitytype] = 'systemuser',
					[owninguser_entitytype] = 'systemuser',
					[owningbusinessunit_entitytype] = 'businessunit',
					[rsm_notificationstatus_entitytype] = 'rsm_status',
					[ownerid_entitytype] = 'systemuser'
		FROM		[dbo].[rsm_emailnotification]
		;
	END
	;
GO

Answer 1

Hi @Bobby P

As Naomi and Erland say, you need to use virtual tables in triggers.

You can refer to this post.

https://www.mssqltips.com/sqlservertip/7429/sql-triggers-for-inserts-updates-and-deletes-on-a-table/

Best regards,

Percy Tang

Answer 2

You need to INNER JOIN with inserted on the Primary key so you would only update the newly inserted rows and not the whole table