Retrieve Print from a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Retrieve Print from a trigger

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-14 : 08:59:48

Hi,
I need to plan a log file that will record insert,update and delete actions.
I want to use trigger for that. How can i retrieve Print (inside the trigger func) from my VB code (to enable me to isert the string into a log file)?
Thanks
Yossi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-14 : 09:30:17

Have the trigger insert the information into a table and then have your VB code SELECT from that table. Alternately you could have the trigger write to the log file directly using bcp and/or xp_cmdshell.

Using PRINT inside a trigger is not a great idea, certainly not if you need to get results back. Additionally, I'd recommend that you have the logging take place entirely within the SQL Server process and not require the VB component to do any of it; it only makes the process needlessly complicated.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-14 : 09:30:29

Try putting the entries into an audit table from the trigger then reading that from the VB app.
You might then decide that the file is not necessary.

The trigger will lock any records that it is changing so they won't be available until after the trigger has completed.

You could also write directly to the log file from the trigger - but then updates would not take place if the log file is unavailable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-14 : 09:41:47

so,
What do you think is the best way to log each action taken place such as insert/update/delete with remarks added?
Thanks
Yossi

quote:

Try putting the entries into an audit table from the trigger then reading that from the VB app.
You might then decide that the file is not necessary.

The trigger will lock any records that it is changing so they won't be available until after the trigger has completed.

You could also write directly to the log file from the trigger - but then updates would not take place if the log file is unavailable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-14 : 09:57:57

Without more details, like table structures for both regular and audit tables, and what kind of information you want to log, we can't even guess what to recommend.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-14 : 10:10:39

Very sorry for the lack of info.
I need to log very basic actions taken place on specific tables.
for ex. If a user updates field x, an update string with the old and new value of field x should be added to a log file as it happens.
The same for other actions.
Can you add an example how to do it from the trigger?
The file we write into from the trigger can be edit from the application too?
If you need more info please advice.
Thanks again
Yossi

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-14 : 10:33:30

create a table

create table auditlog(tblname varchar(100), action char(1), field varchar(100), oldvalue varchar(100), newvalue varchar(100), lastupdated datetime default getdte(), username varchar(100) default user_name())

then the trigger is

create trigger tbl_tr on tbl for insert, update, delete
as

declare @action char(1)
if exists(select * from inserted) and exists(select * from deleted)
select @action = 'U'
else if exists(select * from inserted)
select @action = 'I'
else
select @action = 'D'

insert auditlog
select 'tbl', @action, 'field1', d.field1. i.field1
from inserted i, deleted d
where i.pk = d.pk
and i.field1 <> d.field1 or (i.field1 is null and d.field1 is not null) or (d.field1 is null and i.field1 is not null)
insert auditlog
select 'tbl', @action, 'field2', d.field2. i.field2
from inserted i, deleted d
where i.pk = d.pk
and i.field2 <> d.field2 or (i.field2 is null and d.field2 is not null) or (d.field2 is null and i.field2 is not null)

you can generate all these statements from the table structure.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources