Re: Triggers to Create Script Files.

From: Steve Robinson <steve_at_samurai1.demon.co.uk>
Date: 1998/10/15
Message-ID: <evYzOt899GA.116@uppssnewspub04.moswest.msn.net>#1/1

Ravi,

The way i would do this is to log it to a local table then when you want a listing you can use either ISQL or BCP. This also lets you query the table whenever you like.

Here is the sample script.

drop table test
go
create table test (

field1 varchar(10) primary key clustered,
field2 varchar(10),
field3 varchar(10)

)
go

drop table Commands
go
create table Commands (
Command text )
go

create trigger testinsert on test for insert as insert into Commands (Command)
select 'insert into test values
('+char(39)+field1+char(39)+','+char(39)+field2+char(39)+','+char(39)+field3 +char(39)+')'
from inserted
go

create trigger testupdate on test for update as insert into Commands (Command)
select 'update test set field1 = '+char(39)+i.field1+char(39)+',field2 = '+char(39)+i.field2+char(39)+',field3 = '+char(39)+i.field3+char(39)  +' where field1 = '+char(39)+d.field1+char(39) from inserted i,deleted d
where i.field1 = d.field1
go

create trigger testdelete on test for delete as insert into Commands (Command)
select 'delete from test where field1 = '+char(39)+field1+char(39) from deleted
go

insert into test values ('number 1','number 2','number 3') insert into test values ('number 11','number 12','number 13') go
update test set field2 = 'test this' where field1 = 'number 1' go
delete from test where field1 = 'number 1' go

                /* Now for the output */

exec master..xp_cmdshell 'isql -n -h-1 -E -w8000 -Q"set nocount on select * from commands" -oc:\output.txt'
exec master..xp_cmdshell 'type c:\output.txt'

I hope this was of use.

Steve Robinson SQL Server MVP.
Steve_at_samurai1.demon.co.uk

mravichandran_at_hotmail.com wrote in message <70336k$9e9$1_at_nnrp1.dejanews.com>...
>Dear Netters,
>
>My objective is create a trigger attached to a table so that i get a script
>file when changes are made to the table. this script file should not be
>erased.. but should be appended when changes are made.
>
>I will deliver a table to my customer in January. after that i will be
>making changes to the table. the customer might be making an equal amount
 of
>changes. 6 months later, i want to give only the changes to made to my
 table
>to the customer or share some changes made by the customer.
>
>So, if i can write a trigger that will write out the changes in the follow
>format to an ASCII file:
>
>INSERT into TABLENAME (...);
>
>DELETE from ....
>
>your thoughts will be greatly appreciated.
>
>you can reach me at ravimahalingam_at_pmsc.com.
>
>with regards
>Ravi.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
Received on Thu Oct 15 1998 - 00:00:00 CDT