CODEWITHSUNDEEP
t-sqltriggerssql-server-2008-r2
Pistol Pete
Pistol Pete

Reputation: 183

Trigger after insert on two tables

I have the following trigger right now but I now have the requirement to only kick off the trigger if ANOTHER table Facility has a record inserted into it with a LocationID = 'ER'-

As per the comment I've updated the trigger - 

ALTER TRIGGER [dbo].[VoceraOeOrders] ON [dbo].[OeOrders]
FOR INSERT
AS
IF NOT EXISTS (
        SELECT *
        FROM dbo.Facility
        WHERE LocationID = 'ER'
        )
    RETURN

DECLARE @priority VARCHAR(50)
DECLARE @cat VARCHAR(50)
DECLARE @order VARCHAR(50)
DECLARE @procedure VARCHAR(50)
DECLARE @orderid VARCHAR(50)
DECLARE @locationid VARCHAR(10)
DECLARE @roomid VARCHAR(10)
DECLARE @visitid VARCHAR(50)

SELECT @visitid = VisitID
    ,@priority = Priority
    ,@cat = Category
    ,@procedure = OrderedProcedureName
    ,@order = OrderDateTime
    ,@orderid = OrderNumber
    ,@locationid = (
        SELECT LocationID
        FROM dbo.Facility
        WHERE VisitID = @visitid
        )
    ,@roomid = (
        SELECT CASE 
                WHEN RoomTreatmentID IS NULL
                    THEN 'No Room#'
                ELSE RoomTreatmentID
                END
        FROM dbo.Facility
        WHERE VisitID = @visitid
        )
FROM insterted
WHERE @priority = 'STAT'
    AND @cat IN (
        'CT'
        ,'MRI'
        ,'XRAY'
        ,'US'
        ,'RT'
        ,'NUC'
        ,'ECHO'
        )


BEGIN
    DECLARE @msg VARCHAR(500)
    DECLARE @subject VARCHAR(500)

    SET @msg = @procedure + ' - ' + @locationid + ' ' + @roomid + '-' + ' Priority ' + @priority + '.' + 'Order DateTime/Number ' + @order + '/' + @orderid
    SET @subject = 'Vocera Group Name Here'

    EXEC msdb.dbo.sp_send_dbmail @recipients = N'some_email'
        ,@body = @msg
        ,@subject = @subject
        ,@profile_name = 'Alert'
END

Upvotes: 2

Views: 71

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Just add in the beginning of you trigger:

if not exists(select * from facilities where FacilityID = 'ER')
return

You can't stop kicking trigger. You can check for some condition and return from trigger.

Upvotes: 2

Related Questions