Logge ændringer til data på formular

Kan du ikke lave en tabel med et log felt og evt. et autoudfyldt dato/tidsfelt og så lave en kode på afterupdate eller onChange på alle felterne. Den skal køre en sql, som hedder:

INSERT INTO Tabel1 ( LogFelt)
SELECT [forms]![formularnavn]![felt1] & " " & [forms]![formularnavn]![felt2] osv.. AS Expr1;

I stedet for at have alle felterne i forespørgslerne, så er det nok nemmere at styre, som en streng eller i et skjult felt!~)

Der ER stumper af kode på nettet til at lave det som triggers, men så er der tale om PL-SQL og nu ER det en SQL Server vi har.

Men det som du siger er heller ikke langt fra hvad jeg selv havde tænkt på:
Kode på alle felterne (OnChange og OnUpdate), men hvor jeg så kun logger ét felt af gangen. Mere er ikke nødvendigt.

In a Trigger cant you insert the record into a LOG table?

terry: I believe I can, but what does the code look like? THAT'S the question...!

You need a table which contains all of the field in your original table and a datetime field for when the change occured and also a char(6) field for the type of change (INSERT, UPDATE,DELETE)

CREATE TRIGGER dbo.TRG_YourTable ON dbo.YourTable
FOR INSERT, UPDATE, DELETE
AS
DECLARE @Inserted int,
        @Deleted  int,
        @Type char(6)

SELECT @Inserted = Count(*) from inserted
SELECT @Deleted = Count(*) from deleted

SELECT @Type = CASE
  WHEN (@Inserted > 0) and (@Deleted = 0) THEN 'INSERT'
  WHEN (@Inserted > 0) and (@Deleted > 0) THEN 'UPDATE'
  WHEN (@Inserted = 0) and (@Deleted > 0) THEN 'INSERT'
END

IF (@Type IN ('INSERT', 'UPDATE'))

INSERT INTO YourLogTable
SELECT *, current_timestamp, @Type FROM inserted

ELSE

INSERT INTO YourLogTable
SELECT *, current_timestamp, @Type FROM deleted

RETURN

Yep, arrived at aproximately the same code....but I need some server variables......the name of the user who performed the update/delete.

suser_sname

Not quite sure how that would look in this code:

CREATE TRIGGER DataUpdated
ON MyTable
FOR UPDATE AS INSERT
MyLogTable
SELECT GETDATE(), '',  '', 'Data updated' Operation FROM inserted

dont you need a , between 'Data updated' Operation ?

CREATE TRIGGER DataUpdated
ON MyTable
FOR UPDATE AS INSERT
MyLogTable
SELECT GETDATE(), '',  '', 'Data updated', Operation, suser_sname FROM inserted

The problem I see is that SQL Server complains about the syntax.
(the Operation shouldn't have been there)

I have NO column called suser_sname!
I want to input the name of the user who changed the data into the column called Username

Sorry, suser_sname is a function....sorry
Is it also possible to get the name of the computer the user was logged in on?

I dont think so, the PC could in theory be anywhere in the world, but the user must log on to get access to the dB.

You could put it in the original table then you have access to it inthe trigger.

Yeah, thought so/thought about that...
Anyways, thanks a BUNCH, terry and spg.

Just 1 more Q:
How do I concatenate a date and a string?

CREATE TRIGGER DataUpdated
ON MyTable
FOR UPDATE AS INSERT
MyLogTable
SELECT GETDATE(), suser_sname(), 'Data updated: ' + [Dato] + '/' + [customername] FROM inserted

you will need to convert the datetime field to type char

Convert(char(25), [Dato])

Again, looking around the web (haven't used THAT word in a loooong time!), I couldn't find any easy solution, so I figured it'd be something with the Convert function