Results 1 to 3 of 3
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Additional question on first trigger, returns "DBO" instead of user name, "SA".

    Database is Access 2007 front end and SQL Server 2008 R2 back end. This is prototype development only. I am directly on the server and I am the only user. I am not well experienced with SQL Server. The code in my previous request is working. The trigger fires and functions fine with the exception that it posts "DBO" in the CreatedBY field and "DBO" in the ModifiedBY field instead of the User Name. So I tried this, I am logged on as SA and when I run the query:



    select
    HOST_NAME()as HostName,
    SUSER_SNAME() LoggedInUser

    It returns "SA" not "DBO." The code below returns "DBO" and not "SA". I don't understand why. So far I have tried every variation on SUSER_SNAME to no avail. I am also struggling my way through DOMAIN and AD set up and security so I may have a problem there instead.

    Code:
    CREATE TRIGGER [trg_CreateModifyMachine]
    ON dbo.PROPERTY
    AFTER INSERT,UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;
    Declare @now datetime
    Declare @user varchar(50)
    SELECT
    
    @now = GETDATE()
    ,@user = SUSER_SNAME()
    
    UPDATE T SET
    CreatedDT = COALESCE(I.CreatedDT, @now)
    ,CreatedBy = COALESCE(I.CreatedBY, @user)
    ,ModifiedDT = @now
    ,ModifiedBy = @user
    FROM inserted I
    JOIN dbo.Property as T
    on T.PropertyID = I.PropertyID
    END
    GO
    As always, all help is welcome.

    Fred

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you explain what you're trying to do?

    It looks to me like you have a 'changes' type table that is recording what was modified but I'm not sure why you would carry a 'createddt' and 'createdby' on each modified record. The creation date and person should never change so is there a reason that's not on the 'main' table? (I know that's not part of the problem you're encountering but it's really not necessary and would make your update statement a lot simpler). You're basically carrying the same two files through every modification of the data which is unnecessary.

    So in theory your 'modified' table should carry a PK of it's own, a FK to your actual data table, the user and the date/time stamp the change was made.

    If you can identify the 3 pieces of information you can actually handle that on the Access side and not worry about a trigger on the SQL side (access has it's own method of capturing the user)

    If you want to do it with a SQL trigger are you sure your @now and @SUSER_SNAME are getting the values you want? it doesn't sound like it. If this is happening on the server the SQL it's probably capturing the admin (DBO id) rather than the person trying to make the change, do you have a user DBO set up for your database? Have you tried SYSTEM_USER?

    http://blog.sqlauthority.com/2007/10...ogged-in-user/

    Sorry I don't have access to my SQL test server except on monday/tuesday so I'm just throwing a bunch of stuff out.

  3. #3
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Hi rpeare: The Client Business Rule states that each table will contain the additional 5 fields, CreatedDT, CreatedBY, ModifiedDt, ModifiedBy, MachineID. This was given to me as a requirement. This is an extensive architecture with a lot of tables, dockets, attorneys, and others creating and entering information at different levels. The CreationDT and CreationBY may change further down the related tables as additional information and records are added by other people. The architecture has been developed in ModelRight. If I can get the trigger correct, I can code it in Modelright and push it to all tables in the creation process of the database rather then manually create it for each table. Much easier. The required fields do not exist outside of the table with the records. There is no separate audit table which would contain a primary key other then the primary key of the table the five fields reside in. They do not want to be able to roll-back like in an audit situation they simply want to know over the years who created any particular record and who modified it last. You may be correct on the DBO id. I will also try SYSTEM_USER. You may have missed the previous post regarding this problem. The trigger suggestion is from Pbaldy. I will check out your link as well. Thank you for your time and input. I appreciate it. Always welcome. Please don't close this out yet.

    Fred

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  2. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  3. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  4. Replies: 16
    Last Post: 07-22-2011, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums