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