SQL - Logging updated and inserted records

Question

Looking to audit rows updated and inserted.

When running a script, is it possible to capture the '(x row(s) affected)' etc.

For example

INSERT INTO test_table (name, age)
VALUES ('Patrick', 22);

UPDATE test_table
SET age = 33
WHERE name = 'Patrick' 

'(1 row(s) affected)'

Then insert into an audit table. XXX Being the '(1 row(s) affected)'.

INSERT INTO audit_table (table_used, records_changed)
VALUES ('test_table', XXX);

I think `@@ROWCOUNT` will give you the number of rows affected by the last SQL statement. But not sure about complete message. — Mahesh, Sep 21 '18 at 10:54
`@@RowCount` will suffice if you only want the number of rows "affected" and only your code with the logging functionality will touch the table. (Note that the number may not be what you expect, e.g. an `update` may "affect" 30 rows, but only change values in 10.) A traditional approach for logging _before_ and _after_ values using triggers is shown in [this](https://stackoverflow.com/questions/11890868/how-to-store-historical-records-in-a-history-table-in-sql-server/11891352#11891352) answer. Modern versions of SQL Server have built-in auditing functionality that may be helpful. — HABO, Sep 21 '18 at 18:48

score 0 · Answer 1 · answered Sep 21 '18 at 10:55

@@ROWCOUNT

DECLARE @RowCount1 INT;

INSERT INTO test_table (name, age)
VALUES ('Patrick', 22);

UPDATE test_table
SET age = 33
WHERE name = 'Patrick' 

SELECT @RowCount1 = @@ROWCOUNT

INSERT INTO audit_table (table_used, records_changed)
VALUES ('test_table', @RowCount1);

score -1 · Answer 2 · edited Sep 21 '18 at 11:17

-1

Alternatively, you can use trigger to see changing rows.

CREATE TRIGGER test_table_aut
 AFTER 
 UPDATE 
 ON test_table
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
DECLARE
  d_user_id NUMBER(8) := FND_GLOBAL.USER_ID;
  d_date    DATE      := SYSDATE;
  d_error    VARCHAR2(2000);
BEGIN

  INSERT INTO audit_table (
    updated_by,
    update_date,
    old_name,
    old_age              
    new_name,
    new_age
    )
  VALUES (
    d_user_id,
    d_date,
    :OLD.name,
    :OLD.age,
    :NEW.name
    :NEW.age
);

  EXCEPTION WHEN OTHERS THEN
    d_error := SQLERRM;
    INSERT INTO xx_error VALUES('test_table_aut '||d_error);
END;
/

So you can obtain such as below output.

edited Sep 21 '18 at 11:17

GYaN

2,327
4
19
39

answered Sep 21 '18 at 11:11

Ogzhn d

1
1

what version of sql server is FND_GLOBAL.USER_ID and SYSDATE introduced ? – GuidoG Sep 21 '18 at 11:23
Also the `:= ` seems odd for sql server – GuidoG Sep 21 '18 at 11:26
i am using oracle 10g.it's old one.It may be difference in other versions. – Ogzhn d Sep 21 '18 at 11:47
but the question of the OP is about sql-server, not about oracle – GuidoG Sep 21 '18 at 11:48
3

It would be best to remove your answer because your talking about a different database here – GuidoG Sep 21 '18 at 11:49

SQL - Logging updated and inserted records

2 Answers2