Saving "last changed date" and "last changed user"

T

Thomas Pagel

Hi,

I have a form based on a SQL Server table. I want to save who saved the last
changed on a record and when this change was committed.

The trigger in SQL Server is easy, it worked fine with i.e. Enterprise
Manager.

When I try to change a record in Infopath I get an error when submitting the
changes that the underlying data has been changed and so the data could not
be saved. I guess that Infopath gets confused about the trigger and thinks
that someone else changed data and stops saving the changes for security
reasons.

What can I do? Is there something I can change that this trigger works with
Infopath? Another approach would be to save the username/time in Infopath but
how do I find out the current user and get this saved into a field? I found
some instructions but they were very "generic", I simply didn't understand to
set them up...

Thanks,


Thomas
 
H

Harald Dunkel

Thomas said:
Hi,

I have a form based on a SQL Server table. I want to save who saved the last
changed on a record and when this change was committed.

The trigger in SQL Server is easy, it worked fine with i.e. Enterprise
Manager.

When I try to change a record in Infopath I get an error when submitting the
changes that the underlying data has been changed and so the data could not
be saved. I guess that Infopath gets confused about the trigger and thinks
that someone else changed data and stops saving the changes for security
reasons.

What can I do? Is there something I can change that this trigger works with
Infopath? Another approach would be to save the username/time in Infopath but
how do I find out the current user and get this saved into a field? I found
some instructions but they were very "generic", I simply didn't understand to
set them up...

Thanks,


Thomas
Click to expand...
Hi,
in the OnLoad Event write following code:

var objNetwork = new ActiveXObject("WScript.network");
XDocument.DOM.selectSingleNode("/my:myFields/my:UserName").text =
objNetwork.UserName;

Harald
 
S

Steve van Dongen [MSFT]

Hi,

I have a form based on a SQL Server table. I want to save who saved the last
changed on a record and when this change was committed.

The trigger in SQL Server is easy, it worked fine with i.e. Enterprise
Manager.

When I try to change a record in Infopath I get an error when submitting the
changes that the underlying data has been changed and so the data could not
be saved. I guess that Infopath gets confused about the trigger and thinks
that someone else changed data and stops saving the changes for security
reasons.
Click to expand...

No, I don't think that's it. What does your trigger code look like?

Regards,
Steve
 
T

Thomas Pagel

Steve,

here you are...

CREATE TRIGGER tu_KPI_Fakten ON dbo.KPI_Fakten
FOR UPDATE
AS

DECLARE @@timestamp datetime, @@username varchar(64)

-- get the users logon id
SELECT @@username=system_user

-- get the current date / time
SELECT @@timestamp=GETDATE()

UPDATE kpi_fakten
SET Aenderung_Ist_Zeit=@@timestamp,
Aenderung_Ist_User=@@username
FROM inserted , deleted
WHERE kpi_fakten.kpi_fakten_id=inserted.kpi_fakten_ID
and kpi_fakten.kpi_fakten_id=deleted.kpi_fakten_id
and isnull(deleted.ist,-9999999999)<>isnull(inserted.ist,-9999999999)

UPDATE kpi_fakten
SET Aenderung_soll_Zeit=@@timestamp,
Aenderung_soll_User=@@username
FROM inserted , deleted
WHERE kpi_fakten.kpi_fakten_id=inserted.kpi_fakten_ID
and kpi_fakten.kpi_fakten_id=deleted.kpi_fakten_id
and
isnull(deleted.soll,-9999999999)<>isnull(inserted.soll,-9999999999)




This trigger works fine with i.e. Enterprise Manager. I'm using some German
words for the fields... "Soll" means "plan", "Ist" means "actual", "fakten"
means "facts" (I'm building a fact table for a cube used with the balanced
scorecard MOSA application), "Zeit" means "time"... So I need two updates
for recording changes for actual and plan numbers...

Thanks,



Thomas
 
S

Steve van Dongen [MSFT]

There's only one problem related to triggers that I know of. You
should have 'SET NOCOUNT ON' at the top to make sure InfoPath doesn't
get confused about how my rows were updated, but that's unlikely to be
the cause of this problem.

My best guess is that it is related to the 2 update statements. Try
combining them into 1.
UPDATE kpi_fakten
SET Aenderung_Ist_Zeit=@@timestamp,
Aenderung_Ist_User=@@username
FROM inserted , deleted
WHERE kpi_fakten.kpi_fakten_id=inserted.kpi_fakten_ID
and kpi_fakten.kpi_fakten_id=deleted.kpi_fakten_id
and
Click to expand...
(
isnull(deleted.ist,-9999999999)<>isnull(inserted.ist,-9999999999)
or
isnull(deleted.soll,-9999999999)<>isnull(inserted.soll,-9999999999)
)

What is the actual error message you get?

Regards,
Steve
 
Top