Creating Trigger on SF_Users Table cause dashboard login to fail
I created a trigger on SF_USERS for an "audit" like table to keep track of when users login etc since the Users table only keeps the most recent data...And in doing so...you can no longer log into the dashboard.
The SQL seems correct...
CREATE TRIGGER sf_users_trigger
ON sf_users
AFTER UPDATE
AS
Begin
INSERT INTO sf_users_history (user_name, last_login_ip, last_login_date, last_activity_date, id)
SELECT d.user_name, d.last_login_ip, d.last_login_date, d.last_activity_date, d.id
FROM inserted i
INNER JOIN deleted d on i.id = d.id
END
It inserts into the table sf_users_history. But then I get bounced to an error page and am not able to access the dashboard. Is there a way I can do this? or does the trigger interfere with the authentication process?
Try setting "SET NOCOUNT ON" in the trigger.
added SET NOCOUNT ON after the begin and still got the error
...
AS
Begin
SET NOCOUNT ON
INSERT INTO
...
still get an error...the url is erroring out on page /Sitefinity/Authenticate/SWT which leads me to believe it is not returning the authentication properly.
Just realized what my issue was...I forgot to add an Identity column to my [sf_users_history] table....and was using the uniqueidentifier id as the primary key...the update was trying to insert the same primary key and getting a violation each time...thus the error.
for those that would like to do something similar... I created a table
CREATE TABLE [dbo].[sf_users_history](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_name] [nvarchar](255) NULL,
[last_login_ip] [varchar](50) NULL,
[last_login_date] [datetime] NULL,
[last_activity_date] [datetime] NULL,
[sf_users_id] [uniqueidentifier] NOT NULL,
CONSTRAINT [pk_sf_users_history] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
And a trigger
CREATE TRIGGER sf_users_trigger
ON sf_users
AFTER UPDATE
AS
Begin
SET NOCOUNT ON
INSERT INTO sf_users_history (user_name, last_login_ip, last_login_date, last_activity_date, sf_users_id)
SELECT d.user_name, d.last_login_ip, d.last_login_date, d.last_activity_date, d.id
FROM inserted i, deleted d
WHERE i.id = d.id and d.last_login_date <> i.last_login_date
SET NOCOUNT OFF
END
This is working now to create an audit table so to speak for a users login history...
Hi Dustin,
Personally I wouldn't like the idea of touching the Sitefinity database structure (meaning: adding a trigger on their table). I would rather subscribe to the UserUpdated event and then write to your custom table. Good to see you have this working though!