Limit when a trigger runs
-
Is there any way to limit the trigger, so that it only runs when the app is a .Net SqlClient Data Provider? Or ...not to run if the app starts with SQLAgent or SQL Server Management Studio?
(looking at a trace "ApplicationName" column.)
Thanks
-
dwilliscp (7/26/2013)
Is there any way to limit the trigger, so that it only runs when the app is a .Net SqlClient Data Provider? Or ...not to run if the app starts with SQLAgent or SQL Server Management Studio?(looking at a trace "ApplicationName" column.)
Thanks
very unusual request;
i'm thinking the problem is not the calling program, but how the trigger is written?
does the trigger expect single row inserts, and when you call it from SSMS, it doesn't behave right?
why would work being done from SSMS need to be processed differently if the calling app is something specific?
show us the trigger, i'll bet we can fix it without trying to figure out if the program name /application name is "right" or not;
it's possible to change that Application Name to anything you want, including blanks or a custom string, so that is not a reliable item to base business logic off of:
here's how it's changable in SSMS:
and any application that wants to, it's just part of the connection string:
Dim mySqlConnectionFormat As String = "data source={0};
initial catalog={1};
user id={2};
password={3};
Trusted_Connection=False;
Connect Timeout=600;
Workstation ID=GhostInTheMachine;
Application Name=HaxxorPadPlusPlus;"
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The trigger was created so that when the app updates the table, it also updates the user name and Date/Time. The bad news is that the app will not do this.. the user has to enter the data or it does not get updated. Now we also have a job that runs every 30min updating the data in this table, so when it runs.. it crashes.
I will show the trigger first.. and then the create table.
CREATE TRIGGER [dbo].[BORUpdate1]
ON [dbo].[zt_BOR]
AFTER UPDATE
AS
BEGIN
IF EXISTS (SELECT (1) FROM [zemeter.net].dbo.zt_BOR WHERE [Document]=(select [Document] from inserted) and [Item]=(select [Item] from inserted))
BEGIN
UPDATE [zemeter.net].dbo.zt_BOR
SET [Last_Update_User]=SUBSTRING(system_user, 8, 15),[Last_Update_Time]=current_timestamp
Where [Document]=(select [Document] from inserted) and [Item]=(select [Item] from inserted)
END
END
-----------------------------
CREATE TABLE [dbo].[zt_BOR](
[Plant] [varchar](10) NULL,
[Document] [varchar](10) NOT NULL,
[Item] [varchar](6) NOT NULL,
[Customer] [varchar](1009) NULL,
[Material] [varchar](1009) NULL,
[SL No] [varchar](10) NULL,
[Old RDD] [datetime] NULL,
[New RDD] [datetime] NULL,
[Order Qty] [float] NULL,
[Prev Qty Confirmed] [float] NULL,
[New Qty Confirmed] [float] NULL,
[SU] [varchar](10) NULL,
[DlBl] [varchar](1000) NULL,
[Created On] [datetime] NULL,
[Old Load Date] [datetime] NULL,
[New Load Date] [datetime] NULL,
[MRP] [varchar](10) NULL,
[Source Plant] [varchar](10) NULL,
[Review] [bit] NULL,
[Comments] [varchar](250) NULL,
[Last_Update_User] [varchar](50) NULL,
[Last_Update_Time] [datetime] NULL,
[Days_On_List] [int] NULL,
[Con_Days_On_List] [int] NULL,
[CSR] [varchar](2002) NULL,
[TP Date] [datetime] NULL,
CONSTRAINT [PK_zt_BOR_New] PRIMARY KEY CLUSTERED
(
[Document] ASC,
[Item] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-
I can disable the trigger... then once the job is finished enable.. but that means that any user activity during that time will not get the user/DT that is critical when they question a process change made due to this data.
-
my first thought, is that this trigger is checking things that don't make sense in that WHERE statement, and can be updating rows that it should not.
of course the [Document] in the current Inserted set exists, but it might also exist in other rows too...so it's going to update rows outside of the limited # or rows being updated.
your trigger assumes system_user is always a domain name,and will fail the trigger/transaction for SQL users that have names shorter than 8 characters., so "sa" errors out in this trigger, which is probably what you are reporting.
chop up the name conditionally, like this:
SELECT system_user, --DISNEY\lizaguirre
CASE
WHEN CHARINDEX('\',system_user) > 0
THEN SUBSTRING(system_user,CHARINDEX('\',system_user) + 1,128)
ELSE system_user
END AS PartialName
for the trigger, i would re-write it like this:
CREATE TRIGGER [dbo].[BORUpdate1]
ON [dbo].[zt_BOR]
AFTER UPDATE
AS
BEGIN
UPDATE MyTarget
--SET MyTarget.[Last_Update_User] = SUBSTRING(system_user, 8, 15), --hardcoded to assume an 8 character "domainX\"? would be incorrect/fail if "sa", or SQl user is involved.
SET MyTarget.[Last_Update_User] = CASE
WHEN CHARINDEX('\',system_user) > 0
THEN SUBSTRING(system_user,CHARINDEX('\',system_user) + 1,128)
ELSE system_user
END,
MyTarget.[Last_Update_Time] = current_timestamp
FROM [zemeter.net].dbo.zt_BOR MyTarget
INNER JOIN INSERTED
--the PK of this table([Document],[Item])
ON MyTarget.[Document] = INSERTED.[Document]
AND MyTarget.[Item] = INSERTED.[Item]
END --TRIGGER
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The where looks at the two columns that make up the PK... You code is more compact though.
But I am looking for a better way to not run the trigger ... because this means that if the job fails.. or someone is modifying the job, they have to remember to never run the job outside of the agent.
Side note: IT shut down all of our SQL Auth this year, so everyting will be [Domain]\.
-
dwilliscp (7/26/2013)
The where looks at the two columns that make up the PK... You code is more compact though.But I am looking for a better way to not run the trigger ... because this means that if the job fails.. or someone is modifying the job, they have to remember to never run the job outside of the agent.
Side note: IT shut down all of our SQL Auth this year, so everyting will be [Domain]\.
scheduled jobs often run as sa, and i'm willing to bet that's where your error is coming from; the code you posted just needed a little more bulletproofing, in my opinion.
can you tell us what specific error gets returned when run outside of your application?
otherwise, if you REALLY want that skipping the logic inside the trigger, then the code looks like this:
CREATE TRIGGER [dbo].[BORUpdate1]
ON [dbo].[zt_BOR]
AFTER UPDATE
AS
BEGIN
IF PROGRAM_NAME() IN ('.Net SqlClient Data Provider')--easier to add other potential programs.
BEGIN
UPDATE MyTarget
--SET MyTarget.[Last_Update_User] = SUBSTRING(system_user, 8, 15), --hardcoded to assume an 8 character "domainX\"? would be incorrect/fail if "sa", or SQl user is involved.
SET MyTarget.[Last_Update_User] = CASE
WHEN CHARINDEX('\',system_user) > 0
THEN SUBSTRING(system_user,CHARINDEX('\',system_user) + 1,128)
ELSE system_user
END,
MyTarget.[Last_Update_Time] = current_timestamp
FROM [zemeter.net].dbo.zt_BOR MyTarget
INNER JOIN INSERTED
--the PK of this table([Document],[Item])
ON MyTarget.[Document] = INSERTED.[Document]
AND MyTarget.[Item] = INSERTED.[Item]
END --IF
END --TRIGGER
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Date7/25/2013 8:41:00 PM
LogJob History (Refresh BOR Table)
Step ID1
ServerNDCASPSQLP03
Job NameRefresh BOR Table
Step NameRun New SSIS Package
Duration00:00:06
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: <Domain>\<user>. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:41:00 PM Error: 2013-07-25 20:41:06.42 Code: 0x00000000 Source: Update BOR Table from Count Table Description: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. End Error Error: 2013-07-25 20:41:06.42 Code: 0xC002F210 Source: Update BOR Table from Count Table Execute SQL Task Description: Executing the query "update zt_BOR set Days_On_List = Cnt.Days_On_List ..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:00 PM Finished: 8:41:06 PM Elapsed: 6.24 seconds. The package execution failed. The step failed.
-
yep, that was because of the construction of your trigger:
the [Document] = (select [Document] FROM INSERTED) would make the old trigger fail if more than one row was updated.
my trigger example resolves that issue, as well as inadvertent updates, and the username being shorter than 8 chars.
so the error could potentially occur even from your .NET application, if rows are updated in sets instead of individually, and would have nothing to do with whether it was the application or a job.
the redesign of the trigger has that issue covered.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Ok, thanks. Since it fixes the multi-row failure.. then how would I put in a IF statement to only execute if the record being written contains a data change in the Comments field? That would get around the inserts and updates we are doing in the batch jobs.. I would not want the batch job execution (no matter who the user is that kicked off the job) to update the Last_Update_User and the Last_Update_Time.
Thanks for your help!
-
that extra requirement is easy;
we have to compare the INSERTED row to the DELETED row, and compare in a WHERE statement:
WHERE INSERTED.[Comments] <> DELETED.[Comments]
here's the trigger model again with that additional change:
CREATE TRIGGER [dbo].[BORUpdate1]
ON [dbo].[zt_BOR]
AFTER UPDATE
AS
BEGIN
UPDATE MyTarget
--SET MyTarget.[Last_Update_User] = SUBSTRING(system_user, 8, 15), --hardcoded to assume an 8 character "domainX\"? would be incorrect/fail if "sa", or SQl user is involved.
SET MyTarget.[Last_Update_User] = CASE
WHEN CHARINDEX('\',system_user) > 0
THEN SUBSTRING(system_user,CHARINDEX('\',system_user) + 1,128)
ELSE system_user
END,
MyTarget.[Last_Update_Time] = current_timestamp
FROM [zemeter.net].dbo.zt_BOR MyTarget
INNER JOIN INSERTED
--the PK of this table([Document],[Item])
ON MyTarget.[Document] = INSERTED.[Document]
AND MyTarget.[Item] = INSERTED.[Item]
INNER JOIN DELETED
--the PK of this table([Document],[Item])
ON INSERTED.[Document] = DELETED.[Document]
AND INSERTED.[Item] = DELETED.[Item]
WHERE INSERTED.[Comments] <> DELETED.[Comments]
END --TRIGGER
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Great.. how would this work with an insert? Would it execute? If so how would we wrap this in an IF statement to only run when updating the row.. not inserting it?
Again thanks for your help.. the designer did not tell me that the app would not stamp the user and date/time, so I had to pull a rabit out of my hat after the go-live.
-
dwilliscp (7/26/2013)
Great.. how would this work with an insert? Would it execute? If so how would we wrap this in an IF statement to only run when updating the row.. not inserting it?Again thanks for your help.. the designer did not tell me that the app would not stamp the user and date/time, so I had to pull a rabit out of my hat after the go-live.
well, the trigger is currently only for AFTER INSERT, so without additional changes, would not do anything at all on INSERT;
assuming you want it to log those same two fields on insert as well, i think i would do it like this:
CREATE TRIGGER [dbo].[BORUpdate1]
ON [dbo].[zt_BOR]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE MyTarget
--SET MyTarget.[Last_Update_User] = SUBSTRING(system_user, 8, 15), --hardcoded to assume an 8 character "domainX\"? would be incorrect/fail if "sa", or SQl user is involved.
SET MyTarget.[Last_Update_User] = CASE
WHEN CHARINDEX('\',system_user) > 0
THEN SUBSTRING(system_user,CHARINDEX('\',system_user) + 1,128)
ELSE system_user
END,
MyTarget.[Last_Update_Time] = current_timestamp
FROM [zemeter.net].dbo.zt_BOR MyTarget
INNER JOIN INSERTED
--the PK of this table([Document],[Item])
ON MyTarget.[Document] = INSERTED.[Document]
AND MyTarget.[Item] = INSERTED.[Item]
INNER JOIN DELETED
--the PK of this table([Document],[Item])
ON INSERTED.[Document] = DELETED.[Document]
AND INSERTED.[Item] = DELETED.[Item]
WHERE INSERTED.[Comments] <> DELETED.[Comments]
--only true on insert
OR (DELETED. DELETED.[Document] IS NULL AND DELETED.[Item] IS NULL)
END --TRIGGER
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Oooo so close...I tried to add an 'ELSE' statement to remove the stamp.. if the App has set 'Review' to 0 or NULL, but got the following error:
Msg 4104, Level 16, State 1, Procedure BORUpdate1, Line 9
The multi-part identifier "INSERTED.review" could not be bound.
Msg 4104, Level 16, State 1, Procedure BORUpdate1, Line 9
The multi-part identifier "inserted.review" could not be bound.
CREATE TRIGGER [dbo].[BORUpdate1]
ON [dbo].[zt_BOR]
AFTER UPDATE
AS
BEGIN
IF PROGRAM_NAME() IN ('.Net SqlClient Data Provider')
BEGIN
IF INSERTED.review = 0 or inserted.review is NULL
BEGIN
UPDATE MyTarget
Set MyTarget.[Last_Update_User] = NULL
,MyTarget.[Last_Update_Time] = NULL
,MyTarget.[Comments] = NULL
END
ELSE
BEGIN
UPDATE MyTarget
SET MyTarget.[Last_Update_User] = CASE
WHEN CHARINDEX('\',system_user) > 0
THEN SUBSTRING(system_user,CHARINDEX('\',system_user) + 1,128)
ELSE system_user
END,
MyTarget.[Last_Update_Time] = current_timestamp
FROM [zemeter.net].dbo.zt_BOR MyTarget
INNER JOIN INSERTED
ON MyTarget.[Document] = INSERTED.[Document]
AND MyTarget.[Item] = INSERTED.[Item]
INNER JOIN DELETED
ON INSERTED.[Document] = DELETED.[Document]
AND INSERTED.[Item] = DELETED.[Item]
WHERE INSERTED.[Comments] <> DELETED.[Comments]
END
END
END --End Trigger
-
um, no.
your if statement makes the trigger no longer support multiple rows.
if i have, say five rows being updated, what row is this supposed to test?
IF INSERTED.review = 0
a great rule of thumb for me is:
triggers never have IF statements, only WHERE statements.
triggers never declare variables.
syntax wise, your error is because you are missing a END for the first IF.. BEGIN.. END block
back to a point i made before, you should not be testing the application name in my opinion.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply