CREATE TRIGGER	[spPHCR_AuditTriggerUsers]
ON		[dbo].[tblPHCR_Users]
FOR		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON

DECLARE	@System_User VARCHAR(30),
	@Action VARCHAR(20)

SET	@System_User = SYSTEM_USER

IF EXISTS (SELECT * FROM inserted)
	IF EXISTS (SELECT * FROM deleted)
		SET @Action = 'UPDATE'
	ELSE
		SET @Action = 'INSERT'
ELSE
	SET @Action = 'DELETE'

INSERT	tblPHCR_Users_Audit
	(
		US_Id,
		US_Surname,
		US_Firstname,
		us_Paynumber,
		US_Level,
		US_eMail,
		US_Event,
		US_ActionedBy,
		US_DateActioned,
		US_ActionTaken
	)
SELECT	x.US_Id,
	x.US_Surname,
	x.US_Firstname,
	x.US_Paynumber,
	x.US_Level,
	x.US_eMail,
	x.US_Event,
	@SYSTEM_USER,
	GETDATE(),
	x.Trigger_ActionTaken
FROM	(
		SELECT	CASE
				WHEN @Action = 'INSERT' THEN 'INSERT'
				ELSE 'UPDATE (new value)'
			END AS Trigger_ActionTaken,
			i.US_Id,
			i.US_Surname,
			i.US_Firstname,
			i.US_Paynumber,
			i.US_Level,
			i.US_eMail,
			i.US_Event
		FROM	inserted AS i

		UNION ALL

		SELECT	CASE
				WHEN @Action = 'UPDATE' THEN 'UPDATE (old value)'
				ELSE 'DELETE'
			END,
			d.US_Id,
			d.US_Surname,
			d.US_Firstname,
			d.US_Paynumber,
			d.US_Level,
			d.US_eMail,
			d.US_Event
		FROM	deleted AS d
	) AS x

quote:

---

Originally posted by Pinto

I am now getting this error.

Additional information: Cannot insert explicit value for identity column in table 'tblPHCR_Users_Audit' when IDENTITY_INSERT is set to OFF.
The statement has been terminated.

Thanks for the info visakh16

Sorted this now - thanks

---