[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Trigger Syntax

Posted on 2014-08-21
3
Medium Priority
?
160 Views
Last Modified: 2014-08-21
I have a table e.g. SalesMaster with a field 'Status'. I want to detect if the status changes to '\' or '*' and then send an email (I know how to do this bit)
Can anyone help with the syntax, and will the asterix cause a problem?
0
Comment
Question by:HKFuey
3 Comments
 
LVL 2

Expert Comment

by:Priya Sudharsan
ID: 40276499
You can write something like this.

CREATE TRIGGER notify_mail
ON SalesMaster
FOR UPDATE
AS
  DECLARE @stschk CHAR(1)
  SELECT @stschk = Status from updated
  IF  @stschk in ('\' , '*')
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @recipients = 'email@yourcompany.com', 
          @profile_name = 'default',
          @subject = 'Status Changed', 
          @body = 'Status Changed.';
    END
GO

Open in new window

0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 2000 total points
ID: 40276522
I would do something like this:

if exists (select * from sysobjects where name = 'myTrigger' and type = 'tr')
    drop trigger myTrigger
go

create trigger myTrigger
on SalesMaster
for update
as
    declare @nvchOldStatus nvarchar(1),
            @nvchNewStatus nvarchar(1)

    select @nvchOldStatus = Status from deleted
    
    select @nvchNewStatus = Status from inserted
    
    -- If there has been a change in the status field
    if @nvchNewStatus <> @nvchOldStatus
    begin
        if @nvchNewStatus in ('\', '*')
        begin
            -- send your email
        end
    end
go

Open in new window

0
 

Author Closing Comment

by:HKFuey
ID: 40276750
Hi Priya, could not get your syntax to work, thanks for trying.

Hi Lee, works perfectly. Thanks very much!

Andy
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question