January 3, 2008 at 10:46 am
Greetings folks. I have a simple update trigger for one column in a table. I would like to save the results of the trigger to a text file so I can email it out every day. The table in question is updated by a job that runs every moring, and the basic set up is as below.
--------Create the test table-------------
IF OBJECT_ID('test','u') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT IDENTITY(1,1),
WO_ID VARCHAR(20) NOT NULL,
RTC SMALLDATETIME NULL
)
GO
--------Create the test trigger-----------
IF OBJECT_ID('tr_test','tr') IS NOT NULL
DROP TRIGGER tr_test
GO
CREATE TRIGGER tr_test
ON test
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
IF UPDATE(rtc)
BEGIN
SELECT
i.WO_ID,
RTC = CONVERT(CHAR(10),i.RTC,101)
FROM inserted i,
deleted d
WHERE i.wo_id = d.wo_id
AND ((i.rtc IS NOT NULL AND d.rtc IS NULL) OR (i.rtc <> d.rtc))
END
GO
--------Populate the test table----------
INSERT INTO test(wo_id)
SELECT '101' UNION ALL
SELECT '102' UNION ALL
SELECT '201' UNION ALL
SELECT '202' UNION ALL
SELECT '203'
--------See the results-------------------
UPDATE test
SET rtc = GETDATE()
WHERE ID IN (1,3,5)
The results based on the sample data here are exactly what I need in a text file, just the wo_id and the rtc date columns only. I know this can be done using xp_cmdshell, but I can not find any entries here on how to do it. Also, the only reason I want to put it in a text file for email, is that I can not figure out how to make the output into the @body variable of my email procedure. So say, for example, that I have results as the above code produces. I would want to make them to be something like
'101' + ' ' + '1/3/2008' + CHAR(10)
'201' + ' ' + '1/3/2008' + CHAR(10)
'203' + ' ' + '1/3/2008'
then store them as a variable to be inserted into the body column of my email staging table. Basically I would want to concatenate both the two collumns in the output, then concatenate all the rows, separated by CHAR(10), so they would look nice in the body of an email. Maybe this is a pipe dream? I am not sure how I would get a function to do that, but I am also not sure if it is possible to use a function in a trigger. Anyone have any ideas, or places I could look for help?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 3, 2008 at 1:06 pm
Ok, I think I don't need the xp_cmdshell thing to make a text file(would still like to know how to do, though) I figured out that I can indeed call a function from within a trigger, so I solved my problem as follows
1) altered the trigger to dump the output into a permanent table called NewRTC
IF OBJECT_ID('tr_test','tr') IS NOT NULL
DROP TRIGGER tr_test
GO
CREATE TRIGGER tr_test
ON test
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
IF UPDATE(rtc)
BEGIN
IF OBJECT_ID('NewRTC','u') IS NOT NULL
DROP TABLE NewRTC
SELECT
Constant = 1,--added only to have a value around which to concatenate
i.WO_ID,
RTC = CONVERT(CHAR(10),i.RTC,101)
INTO NewRtc --Maybe there is a better way. Could not use # because of function
FROM inserted i,
deleted d
WHERE i.wo_id = d.wo_id
AND ((i.rtc IS NOT NULL AND d.rtc IS NULL) OR (i.rtc <> d.rtc))
--Just a test to see if I can call a function from within a trigger
SELECT DISTINCT
dbo.fnTest(constant)
FROM NewRTC
END
GO
2. Created the concatanation function (format stollen directly from one of Jeff Moden's examples) that will make the output look real nice in an email (tested and worked)
IF OBJECT_ID('dbo.fnTest','fn') IS NOT NULL
DROP FUNCTION dbo.fnTest
GO
CREATE FUNCTION dbo.fnTest (@constant INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return ,'') + WO_ID + ' ' + RTC + CHAR(10)
FROM NewRTC
WHERE constant = @constant
RETURN @Return
END
So...I guess I am all set. I can easily work with what I have to populate the email staging table directly from the trigger. The only other question I can think of, is there maybe another way to do without having to create a permanent table? Thanks, and sorry for the false alarm, I should have done my homework first.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 3, 2008 at 6:57 pm
Yes... must it be a function or can you use a stored procedure to do the concatenation? Store procedure can use a temp table that is defined before the sproc is called and still be available from the calling routine.
Another "trick" is that if you really need to, you can, in fact, create a temporary stored procedure. Not sure that's what's needed here, but it is an option.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2008 at 6:55 am
Jeff Moden (1/3/2008)
Yes... must it be a function or can you use a stored procedure to do the concatenation?
I did not know you could do that. Functions are still a little over my head in terms of how they work. You helped me with a CSV function a while back on the other forum, and I just altered it to work here because that's all I knew how to do. So...are you saying I can have a procedure do what this functions does? I will start fiddling. Thanks Jeff.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 4, 2008 at 7:16 am
So...are you saying I can have a procedure do what this functions does?
Well, kind of... before SQL Server 2000, there was no such thing as user defined functions... people ended up using While loops that called stored procedures that did the work of a "function" (although there are some setbased ways to get around that problem, in some cases)... comparitively very slow and I'm actually sorry I mentioned it. It's something you usually want to avoid.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2008 at 2:18 pm
Duly noted. I will stick with the function as long as it works. Thaks Jeff.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply