Trigger UPDATE write TXT file

Question

How can I create a Rigger that is fired every time I have one UPDATE in x column, grab the new value create new txt file and insert inside it. ( for synchronization purposes ).

For example:

produtos

Layout do txt:
55 | nome_produto | quantidadeEstoque | peso

In update events, it takes some values and inserts this layout into a new txt.

Until the part of Trigger and etc I created, however, I do not know how to work with the manipulation of TXT.

Answer 1

The best known method is create a common Trigger and use the command master..xp_cmdshell to execute any command on the operating system environment which in turn save the data to the desired file and location.

Here are some methods to create text based file in this arithmetic:

BCP

It is an SQL tool that executes a query and writes to a text file. Example:

master..xp_cmdshell 'bcp banco..tabela out c:\arquivo.bcp -S -U -P -c '

Redirect output to a file

It is basically to use opperational system commands to generate the file. Example:

exec master..xp_cmdshell 'echo meu-texto-aqui > c:\arquivo.txt'

Note that it is possible to call any program and pass some value using a variable, for example.

Considerations

Using a Trigger to record to a file is a bad idea, as you will be directly affecting the performance of the database and, consequently, of the system, outside of the user response time.

An alternative solution is to write the data in an auxiliary table or use a control flag to then export the new data in a periodically executed task, for example, every N minutes.