July 11, 2013 at 1:12 pm
I have a trigger that emails the user whenever their order has been marked as complete. This works fine.
Now, I'm working on a different application that modifies the table on which the trigger is set. The new application will never change the production status of the order, so the trigger isn't relevant. However, when the new application attempts to modify the table, an error occurs because the users of the new application don't have access to the database where the trigger acquires the user's email address.
I thought I had successfully coded the trigger in such a way that it would exit the trigger if the ProductionStatus bit field isn't set to 1, but I still receive the error message when modifying the table with the new application. Is there a way to avoid this error or will it still check permissions on the table even when that line of code isn't going to run?
USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[utr_ProductionStatusNotify] ON [dbo].[ProductionStatus]
FOR UPDATE
AS
BEGIN
DECLARE @SUBJECT as NVarChar (100)
DECLARE @BODY as NVarChar (2000)
DECLARE @ESTIMATOR As NVarChar(50)
DECLARE @ESTIMATOREMAIL As NVarChar(100)
DECLARE @ORDERID As NVarChar(50)
DECLARE @TO As NVarChar (100)
DECLARE @FROM As NVarChar (100)
DECLARE @CUSTOMERID AS NVARCHAR(100)
DECLARE @CUSTOMERNAME AS NVARCHAR(100)
DECLARE @JOBNAME AS NVARCHAR(100)
IF (SELECT ISNULL(i.ProductionComplete, d.ProductionComplete) FROM Inserted i
FULL JOIN Deleted d on i.OrderID = d.OrderID
WHERE ((IsNull(i.ProductionComplete, '') <> IsNull(d.ProductionComplete, '')))) = 0
BEGIN
RETURN
END
ELSE
SELECT @ORDERID = ISNULL(i.OrderID, d.OrderID) FROM Inserted i
FULL JOIN Deleted d on i.OrderID = d.OrderID
WHERE ((IsNull(i.ProductionComplete, '') <> IsNull(d.ProductionComplete, '')))
SELECT @ESTIMATOR = Estimator FROM Orders_Open WHERE OrderID = @ORDERID
SELECT @ESTIMATOREMAIL = Email FROM [Manko3].[dbo].[Users] WHERE Login = @Estimator
SELECT @CUSTOMERID = CustomerID FROM Orders_Open WHERE ORDERID = @ORDERID
SELECT @CUSTOMERNAME = Customer_Name FROM [Manko3].[dbo].[Customer_Information] WHERE Customer_ID = @CUSTOMERID
SELECT @JOBNAME = JobName FROM Orders_Open WHERE OrderID = @ORDERID
SELECT @SUBJECT = 'Order #' + @ORDERID + ' has been marked as Production Complete'
SELECT @BODY = 'Production has been completed on order #' + @ORDERID +', the ' + @JOBNAME + ' job for ' + @CUSTOMERNAME + '.'
SELECT @TO = @ESTIMATOREMAIL
SELECT @FROM = @FROMEMAIL
EXEC [dbo].[sp_SendEmail] @FROM, @TO, @SUBJECT, @BODY
END
July 11, 2013 at 2:01 pm
Your trigger will still run under the context of the current user, however you can execute it as another login by using the EXECUTE AS option.
Something like this:
ALTER TRIGGER [dbo].[utr_ProductionStatusNotify] ON [dbo].[ProductionStatus]
WITH EXECUTE AS UserWithAppropriatePermission
FOR UPDATE
AS
BEGIN
...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 2:04 pm
But shouldn't the trigger exit prior to getting to the part where the user doesn't have permissions? Or does it automatically check for permissions regardless of whether the code is ever executed?
July 11, 2013 at 2:07 pm
skempf (7/11/2013)
But shouldn't the trigger exit prior to getting to the part where the user doesn't have permissions? Or does it automatically check for permissions regardless of whether the code is ever executed?
It won't just exit at some point. The reason you would use EXECUTE AS is so that when the trigger runs, it runs as though it is the user with elevated permissions instead of the current user that doesn't have access to that table. This allows the user to execute the code without the need to give them permission.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 2:26 pm
What is the point of the RETURN call in the trigger, if it doesn't cause the trigger to end execution?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply