What fires the Trigger?
-
I have a boss who is very impatient and he needs things done yesterday. I have been told to create a Trigger which will notify members of staff about new PartItems which have come in on a particular date. The structure of this model is as follows:
I have a query in Access, this query has the following fields.
PartID-----EBDSC1(Descrprition)----GGOPDT(Date)----GGTTYP(Receipt)
PartID is entered in manually.
EBDSC1 is getting data from a AS400 system
GGOPDT is getting data from a AS400 system
GGTTYP is getting data from a AS400 system
Within Access the query runs and we get the results. For the criteria of GGOPDT I have used Right(Date$(),4)&Left(Date$(),2)&Mid(Date$(),4,2)-1
This will give me todays date minus one so basically it should give me yesterday. This query works fine.
Now when it comes to SQL, its a completely different matter.
I have used DTS to drag the query in and produce the results within a table within my database.
This works fine, I have then gone to the table it has produced and right clicked over it and gone to "All Tasks", I have then gone to "Manage Triggers". I have inserted this trigger into the table.
create trigger PartID on PartIDUpdateNT1 for update
as
declare @PartID int, @msg varchar(100)
if update(GGOPDT)
begin
select @PartID = 0
while @PartID < (select max(PartID) from inserted)
begin
select @PartID = min(PartID) from inserted where PartID > @PartID
if exists (select * from inserted i, deleted d where i.PartID = d.PartID and i.PartID = @PartID and i.GGOPDT <> d.GGOPDT)
begin
select @msg = 'part ' + convert(varchar(20),@PartID) + ' changed'
exec master..xp_sendmail @recipients = 'me@meltd.com,someoneelse@@meltd.com', @subject = 'part date change', @meggase = @msg
end
end
end[\b]
What I want the trigger to do is basicaly mail certain people if a New Part was added yesterday. I want this to be an automated process.
My questions are:
1) If the DTS has run today and then runs tomorrow, where will DTS put tomorrow's table, will it install the data on tHe last table or will it create a new table?
2) If it puts it within the existing table, when the query is run, will it mail out the results which are already in there?
3) If it puts it in a new table what will happen to my trigger, will I have to type in a new trigger for tis table?
4) If I run a DTS and it creates a table, how do I then intergrate the trigger within that table?
5) What will fire the mails across to the specific people?
If you have any new suggestions please feel fre to express them with me because I nee all the help I can get. Even if its a case of starting from scratch.
Please Help.
My life is in your hands!
Thanks
-
DTS will not make up table names at random, it will put the data in the table you've designed it to use. (I know, you can use an ActiveX script step to rewrite the properties of other steps, but I don't think the answer to this problem requires it.)
DTS would not be smart enough to create a trigger when creating the table, and you couldn't add a trigger to the table after DTS has loaded it and have it fire retroactively. The trigger will also not see yesterday's records in the inserted data.
You could put in an SQL task step to run CREATE TABLE and CREATE TRIGGER ddl statements before loading the table, but I don't see why you wouldn't just create the table in advance and have DTS reuse the same table each time.
You show an UPDATE trigger, which would not be fired when DTS inserts new rows.
Assuming the only time rows are added to this table is when the DTS package runs, I see no need for a trigger at all. Put the mail function in the DTS package to run after rows are inserted. Use a parts history table with a DateAdded column. Use CONVERT to remove the time from the date.
CREATE TABLE PartHistory (
DateAdded smalldatetime not null default convert(char(8),getdate(),1),
PartID int not null,
GGOPDT date not null,
...)
Have DTS load the data into this table. You probably want another step that deletes data more than a few days old. I'm assuming that you are looking for either new PartID values or ones where GGOPDT has changed, indicating an update on the AS400. You can identify them with this query:
DECLARE @Today smalldatetime, @Yesterday smalldatetime
SET @Today = cast(convert(char(8),getdate(),1) as smalldatetime)
SET @Yesterday = dateadd(d,-1,@Today)
SELECT a.PartID, a.GGOPDT, ...
FROM PartHistory a
LEFT JOIN PartHistory b ON a.PartID = b.PartID AND b.DateAdded = @Yesterday
WHERE a.DateAdded = @Today AND
(a.GGOPDT <> b.GGOPDT OR b.PartID IS NULL)
Another assumption is that the PartHistory table is updated daily. If a day was skipped, you have to be careful how you define "yesterday".
SELECT @Today = MAX(DateAdded) FROM PartHistory
SELECT @Yesterday = MAX(DateAdded) FROM PartHistory WHERE DateAdded < @Today
There are other forums that discuss how to turn this query into an email. The simplest way is to concatenate rows into a varchar variable:
DECLARE @Msg VARCHAR(8000)
SET @Msg = ''
SELECT @Msg = @Msg + CAST(fld1 AS VARCHAR) + ' ' + CAST(fld2 AS VARCHAR) ...
More elaborate ways include using an XML query, then using XSLT to turn that into HTML email.
-
You don't want to mail from within a trigger.
It will cause you all sorts of problems, grey hair etc.
It is much better to have the trigger just notify that a mail should be sent (ie a queue table or similar) and then have a separate step/proc/job to scan the queuetable and do the mailing out of the way of the transaction.
..just my .02 though
/Kenneth
-
I am going to scrap this and go a completely different route instead. I will use SQL Agent Jobs.
I will create a Query which will then be executed in a job. This job will be scheduled.
This is real simple but I am having problems as I am a newbie when it comes to code.
This is my Query
SELECT * FROM PartUpdate
EXEC master..xp_sendmail 'me@meltd.com',
'New Part Numbers Update.'
As you can probably see I do not get the results from the SELECT Statement.
How do i attach the results to the mail?
Thanks
-
Using the @query argument of xp_sendmail should (if I remember rightly) send the results of a query in the mail (in CSV format I think)...
Exec master.dbo.xp_sendmail @recipients = 'me@meltd.com', @message = 'New Part Numbers Update', @query = 'SELECT * FROM dbo.PartUpdate'
HTH
-
Hi Winash, are you sure it is the @Query argument. Its just that I get the error message
Invalid object name 'PartUpdate'.
-
This is the code:
EXEC master..xp_sendmail @recipients = 'me@meltd.com',
@query = 'SELECT * FROM NewPartNos..PartIDUpdate',
@subject = 'New Part Numbers',
@message = 'The contents of New PartIDUpdate:',
@attach_results = 'TRUE', @width = 250
This is the Error:
Invalid object name 'NewPartNos..Partidupdate'.
I have created a Database called 'NewPartNos' I have pumped data into a table called 'PartIDUpdate'. It still does not work. What else could I be doing wrong??
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply