+1-888-365-2779
Try Now
More in this section

Forums / Developing with Sitefinity / Creating Trigger on SF_Users Table cause dashboard login to fail

Creating Trigger on SF_Users Table cause dashboard login to fail

5 posts, 1 answered
  1. Dustin
    Dustin avatar
    20 posts
    Registered:
    05 Jan 2012
    26 Feb 2014
    Link to this post

    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?

  2. Victor
    Victor avatar
    94 posts
    Registered:
    15 Jun 2011
    26 Feb 2014
    Link to this post

    Try setting "SET NOCOUNT ON" in the trigger.

  3. Dustin
    Dustin avatar
    20 posts
    Registered:
    05 Jan 2012
    26 Feb 2014 in reply to Victor
    Link to this post

    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. 

  4. Dustin
    Dustin avatar
    20 posts
    Registered:
    05 Jan 2012
    26 Feb 2014 in reply to Dustin
    Link to this post

    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...

    Answered
  5. Arno
    Arno avatar
    249 posts
    Registered:
    08 Sep 2010
    26 Feb 2014 in reply to Dustin
    Link to this post

    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!

5 posts, 1 answered