What fires my Trigger? - 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 2000 Forums

Transact-SQL (2000)

What fires my Trigger?

Author

Topic

JonahMk2
Starting Member

21 Posts

Posted - 2004-10-21 : 08:04:57

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.
[b]
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

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-10-22 : 04:11:30

SQL Server Reporting Services may solve your problem, have you looked at that? It may save having to put Outlook onto your production server

To alcohol ! The cause of - and solution to - all of life's problems

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources