SQL Trigger question

[alura]

I think this is the right forum for this... if not can someone point me in the right direction?

I am looking to create a SQL trigger for when a new record is inserted into table 'Dispatch'. the trigger would then send an e-mail to someone saying what the new dispatch was.

I have the code for the insert, but I don't want an e-mail for every new dispatch, I only want one for dispatches which meet a certain criteria (the criteria is when one of the columns in the Dispatch record is equal to 'CIF')... So basically I'm wondering if a trigger can be set to only send an e-mail based on the data in the new record.. Thanks!

Jason

 

[bborissov]

Of ocourse, but what database you use? VFP or SQL Server? You posted in VFP forum, but you talk about triggers and I am not so sure anymore Of course you have triggers in VFP but they are complitely different from that that are in SQL Server.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[alura]

Thanks, my mistake, I am using SQL server 2005 Developer edition. I was confused as to what forum I should post in.

 

[bborissov]

No problem.
Did you want to send this eMail for EVERY inserted record wich match the criteria (columnX = 'CIF') or you want to send ONE mail that some records are inserted (columnX = 'CIF')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[alura]

I am looking for an e-mail for every Dispatch record where column 'Priority' is equal to 'CIF' or 'CI' ... any other new records can be ignored. (CIF means it is a call in - full tank which is important and why we want an e-mail notification).

 

[bborissov]

CREATE TRIGGER [trgSendEmails]
    ON  [dbo].[Dispatch]
    AFTER INSERT
    AS
--- DECLARE some variables where you will receive the info form inserted records

    DECLARE crsInserted CURSOR  FOR 
            SELECT [field list here]  FROM Inserted  WHERE Priority IN ('CIF', 'CI')
    OPEN crsInserted
    FETCH NEXT FROM crsInserted INTO (declared varuables here)
    WHILE @@FETCH_STATUS = 0
          BEGIN
              EXEC master..xp_sendmail (put appropriate poarameters from the declared variables)
              FETCH NEXT FROM crsInserted INTO (declared varuables here)
          END
     CLOSE crsInserted
     DEALLOCATE crsInserted

This is not tested at all, but you get the idea.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[alura]

Thanks! Just one question - I declare any variables I want to use within the text of the e-mail, such as DispatchNumber, Zone, and Priority

So would I do:

Declare DispNum,DispZone

and then...

FETCH NEXT FROM crsInserted INTO DispNum,DispZone ?
And would I only want to select fields that I would want included in the e-mail?
I'm sorry I am very new at this!

 

[bborissov]

Yes, If these are all variables you need then try:

CREATE TRIGGER [trgSendEmails]
    ON  [dbo].[Dispatch]
    AFTER INSERT
    AS
--- Let say you have following fields you need to send
--- DispatchNumber -> Integer
--- Zone -> varchar(200)
--- Priority -> varchar(200)
--- Your declaration section must looks like this:

    DECLARE @DispatchNumber int
    DECLARE @Zone varchar(200)
    DECLARE @Priority varchar(200)

    DECLARE @MyMessage nvarchar(1000) -- This will be the eMail message

    DECLARE crsInserted CURSOR  FOR
            SELECT DispatchNumber, Zone, Priority
                   FROM Inserted 
                   WHERE Priority IN ('CIF', 'CI')
    OPEN crsInserted
    FETCH NEXT FROM crsInserted INTO @DispatchNumber,
                                     @Zone,
                                     @Priority
--- Note the variable must be in the same order as Field list
    WHILE @@FETCH_STATUS = 0
          BEGIN
          SET @MyMessage = N'New record is inserted. 'DispatchNumber = '+CAST(@DispatchNumber as nvarchar(200))+
', Zone = '+@Zone+', Priority = '+@Priority
                         
          EXEC master.dbo.xp_sendmail 
               @recipients=N'SomeBody@SomeWhere.com',
               @message=MyMessage

          FETCH NEXT FROM crsInserted INTO @DispatchNumber,
                                           @Zone,
                                           @Priority
          END
     CLOSE crsInserted
     DEALLOCATE crsInserted

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[alura]

thanks so much for your help. I'll be giving it a try this weekend.

Jason

 

[alura]

I created the trigger and I'm pretty sure the code is perfect. I think I need to adjust a permission somewhere because I now get this message from within our DB program when we create a dispatch with a priority of CI or CIF:

an unexpected 'application-defined or object-defined error: execute permission denied on object 'xp_sendmail', database 'mssqlsystemresource', schema 'sys'. error occured in coastal. 1 error(s) are logged.

Any ideas of where I could adjust the permissions for xp_sendmail?

Jason

 

[alura]

Okay, I thought xp_sendmail was the problem so I switched to sp_send_dbmail, but I still have a permission issue. How can I assign a user execute rights to the sp_send_dbmail stored procedure?