Trigger and more - 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 2005 Forums

Transact-SQL (2005)

Trigger and more

Author

Topic

pyromelana
Starting Member

23 Posts

Posted - 2009-03-09 : 04:01:02

Hi guys!

I have a couple of questions concerning triggers.

1. I want to create a trigger to create a file. E.g. after insert or update of a table the trigger should create a e.g. file.txt in one of the directory. ( 'C:\programs\file.txt')

2. The trigger should insert the result of a sql query in a file and write it in a directory ( 'C:\programs\result.txt' or whatever the file-format can be)

Can anyone give me some hints how to solve these problems or where to read about it? [url]http://msdn.microsoft.com/de-de/default.aspx[/url]
isn't very helpful for me.

Regards

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 04:11:20

Well the best way I can think of doing this is using CLR trigger, that is a .NET assemblies acting as your trigger. But I like to point out since trigger fires everything time something happens on your database each update will/can be very expensive and can have performance affects on your database.

Ref: http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

pyromelana
Starting Member

23 Posts

Posted - 2009-03-09 : 05:29:14

Thx, but your link is not very helpfull or I am too stupid ;-) My .NET knowledge is on a minimum and therefore I can't use aspalliance.com...

Sure, I know about the performance affects such a trigger can evoke.

Anyone else with a result for my questions?

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 13:00:13

Heh .. sorry. I quickly found an example of the idea. If .NET is not your cup of tea; I don't think writing to a file from T-SQL is possible. But how about? Write to a temp with Query ID and results in XML format? Long as it is only inserting it should be okay if your are willing to live with performance impact?

Another link on CLR Integration: http://msdn.microsoft.com/en-us/library/ms345136.aspx (this time M$) :P.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-09 : 14:01:28

Why do you want to do this? Is it for auditing purposes? It is almost always a bad idea to use a trigger to do anything outside the database. Any chance for concurrent users - one locking the others out of the file? Were you intending append to the file or replace it for every trigger fire?

Be One with the Optimizer
TG

pyromelana
Starting Member

23 Posts

Posted - 2009-03-10 : 03:36:26

@TG
It is the database of our ERP-system. And in one of the tables we record the errors or missing fields of e.g. orders. Therefore we would like to automatically send an email to the responsible persons and or to the persons who are involved to the errors including a list with the query result...I don't think, that there will be a performance problem because there aren't many errors. But we have to look into the table daily and with a trigger the process will be automated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 04:16:06

then what you need is a ssis packege which records the errors onto a log table and then use send mail task to sent the table details as mail to persons concerned. you can call this package from sql job and can schedule job accordingly to be executed as per your convienience

pyromelana
Starting Member

23 Posts

Posted - 2009-03-10 : 09:05:40

quote:
Originally posted by visakh16

then what you need is a ssis packege which records the errors onto a log table and then use send mail task to sent the table details as mail to persons concerned. you can call this package from sql job and can schedule job accordingly to be executed as per your convienience

Yes, could be done with a schedule job. But in the sql server-agent you can't set up the job permanently - only like e.g. every 5 minutes. And this is exactly that, what we do not want to have ;-) On the other side triggers work permanently... But thx for your help

xpandre
Posting Yak Master

212 Posts

Posted - 2009-03-10 : 09:39:06

Not sure if this is what you want..workd on 2000, for 2005, xp_cmdshell by default is OFF.You have to go the surface area config and set it to ON

create table d1(a int, b int,c int)


create trigger t1  on d1 for insert


as

declare	@cmd varchar(1000),@a int, @b int, @c int
declare @workfilename varchar(128)

select @workfilename = 'c:\triggerfile.txt'

declare c1 cursor for select a,b,c from d1
open c1
fetch c1 into @a,@b,@c
while (@@fetch_status = 0)
begin	
	
select	@cmd =  'echo '		+ cast(@a as varchar)+','+ cast(@b as varchar)+','+ cast(@c as varchar)
			+ ' >> ' + @workfilename
exec master..xp_cmdshell @cmd
fetch c1 into @a,@b,@c	
end
close c1
deallocate c1


insert into d1 values (1,2,3)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-10 : 10:26:46

A more typical way to handle that type of thing is to validate, optionally reject the order, and notify the user of the problem prior to the database post - either in the stored procedure that does the insert or in the application that makes the database call. A custom error can be associated with an email alert, error message replacement parameters can be used to provide the spepcifics. So the insert SP can do the validation and simply call a RAISERROR (<custom message>). That way the user is notified immediately and a followup email can also be sent via the alert.

One problem with using a trigger to do things like save to file, email, etc, is that errors are likely to happen periodically because of conditions outside the control of the database. Since the trigger is in the scope of the user transaction any error will rollback the order. And since an error caused this to happen the user may not even be notified that their order was rolled back so orders come up missing with no record of it.

You also didn't answer my earlier questions regarding concurrency. You say that errors aren't frequent but one of these days 2 people will post a bad order at the same time, what happens then?

Be One with the Optimizer
TG

pyromelana
Starting Member

23 Posts

Posted - 2009-03-11 : 05:52:39

Ok, I changed my plans... what I did was using a trigger like the code below

ALTER TRIGGER [dbo].[BDEERROR] 
ON  [dbo].[YAUF0135]

FOR INSERT,UPDATE

AS 

SET NOCOUNT ON

declare @message varchar(100)
declare @number INT
SET @number = 0

SET @number = (SELECT COUNT(*) FROM dbo.YAUF0135 WHERE EXISTS (SELECT * FROM dbo.YAUF0135 WHERE ZMODIDAT < DateAdd(mi,10,getdate())AND FEHLERTEXT IS NOT NULL))
--This sql only counts the errors. That is the essential info we need

SET @message='BDE ERRORS (#9***)'

IF @number > 0
BEGIN
EXEC msdb..sp_send_dbmail 

            @recipients = 'status@ri**.com',
	    @subject = 'BDEERRORS ',			
	    @body = @message;
END

We tested it one night, did work but had some performance problems because the night shift didn't enter all mandatory fields in their jobs and therefore created error messages...

We cancelled the trigger job and now created a report without triggers... one pdf-document more in the incoming emails but that was the quickest way to inform the responsible person

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-11 : 08:39:09

Thanks for the update!
Being a db guy my first inclination is to not allow a user to post an order with missing mandatory information. But I guess, in your business, having an incomplete order in the system is more valuable than fewer orders with all mandatory values. Understandable in this economy as long as the orders can be processed.

One thing I'm curious about though, if the users don't fill in all the mandatory fields when they place the order do they even bother to correct them when they receive these error reports?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources