stop delete trigger firing when i'm doing an "admin delete"

Question

stop delete trigger firing when i'm doing an "admin delete"

snomadj

SSCarpal Tunnel

Points: 4491
More actions
July 4, 2018 at 9:27 am

#365257

Sometimes I don't quite have the right words to google or ask for help with …. please bear with. I am grateful for your help.
I have a delete trigger on table A.
A delete triggers an update into table B. This is my audit table.
My users want a proc that does an admin delete - delete from table A without triggering the update into table B. For test data etc.
Is there a graceful way of doing this?
Obvs. I can delete from A. THEN delete from B. It's just not awfully graceful.
Thank you!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99127 More actions · Answer 1

What is an "admin" delete? When the delete is done both a sys admin? If so, why not disable and then re-enable the trigger?
DISABLE TRIGGER dbo.YouurTrigger ON dbo.YourTable;

DELETE FROM YourTable WHERE {Some WHERE clauses};

ENABLE TRIGGER dbo.YouurTrigger ON dbo.YourTable;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 2

Because it's not just me that's going to be doing it.

Standard user group need to be audited. Trigger must fire.
Admin type of user group need to be able to delete test data (via a procedure I'm writing). They are not admin users just slightly more advanced users of their own system.

I may be overthinking it. I can deal with it in the procedure or I could try and get funky in the trigger … somehow. That was the idea I'm trying to get to and probly not explaining very well.

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 3

snomadj - Wednesday, July 4, 2018 10:17 AM
Because it's not just me that's going to be doing it.
Standard user group need to be audited. Trigger must fire.
Admin type of user group need to be able to delete test data (via a procedure I'm writing). They are not admin users just slightly more advanced users of their own system.
I may be overthinking it. I can deal with it in the procedure or I could try and get funky in the trigger â€¦ somehow. That was the idea I'm trying to get to and probly not explaining very well.

Then you may as well turn off the whole audit system and delete all of the audit triggers on the table because you're defeating the whole purpose of an audit table/system whether you're an admin for the system or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 4

snomadj - Wednesday, July 4, 2018 10:17 AM
Because it's not just me that's going to be doing it.
Standard user group need to be audited. Trigger must fire.
Admin type of user group need to be able to delete test data (via a procedure I'm writing). They are not admin users just slightly more advanced users of their own system.
I may be overthinking it. I can deal with it in the procedure or I could try and get funky in the trigger â€¦ somehow. That was the idea I'm trying to get to and probly not explaining very well.

Is this test data in production?
It may not be the case but it sounds like the gist of the issue could be that testing is being done in production.

Sue

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 5

Sue / Jeff, you're both quite right.

We have no test system & no chance of one so I am trying to make the best of that. You've made me re-think & the issue is that the client doesn't want test data showing up in our reporting solution. I'm leaving the audit data as is & adding a flag to the audit tables to determine visibility instead.

Thank you all for your time. I do appreciate it!

Thom A SSC Guru Points: 99127 More actions · Answer 6

snomadj - Thursday, July 5, 2018 1:51 AM
Sue / Jeff, you're both quite right.
We have no test system & no chance of one so I am trying to make the best of that. You've made me re-think & the issue is that the client doesn't want test data showing up in our reporting solution. I'm leaving the audit data as is & adding a flag to the audit tables to determine visibility instead.
Thank you all for your time. I do appreciate it!

That sounds like a much better idea.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 7

snomadj - Thursday, July 5, 2018 1:51 AM
Sue / Jeff, you're both quite right.
We have no test system & no chance of one so I am trying to make the best of that. You've made me re-think & the issue is that the client doesn't want test data showing up in our reporting solution. I'm leaving the audit data as is & adding a flag to the audit tables to determine visibility instead.
Thank you all for your time. I do appreciate it!

Of course, a much better idea would be to get the shop to build a proper test system but, in the absence of such a possibility, that's a great idea. It will not only prevent accidents but, if you ever have to go through audits or you need to "prove" to someone what happened and when, the auditors are going to love you a little more than they would if you continued to allow the deletes. Well done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101076 More actions · Answer 8

To answer the original q, yes, there is a very easy way to "tell" the trigger not to do the INSERTs into table B.

Use specific byte(s) of CONTEXT_INFO to control it. Set them to the special value before you run the DELETE to cancel INSERTs to B:

--in the DELETE trigger code itself
CREATE TRIGGER ...
...
IF NOT SUBSTRING(CONTEXT_INFO(), 4, 4) = 0xFAFBFCFD
BEGIN
INSERT INTO dbo.tableB ( ... )
SELECT ...
END /*IF*/

The actual DELETE code:
SET CONTEXT_INFO 0x000000FAFBFCFD;
DELETE FROM dbo.tableA WHERE ...
SET CONTEXT_INFO 0x00;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 9

ScottPletcher - Thursday, July 5, 2018 8:24 AM
To answer the original q, yes, there is a very easy way to "tell" the trigger not to do the INSERTs into table B.
Use specific byte(s) of CONTEXT_INFO to control it. Set them to the special value before you run the DELETE to cancel INSERTs to B:
--in the DELETE trigger code itself
CREATE TRIGGER ...
...
IF NOT SUBSTRING(CONTEXT_INFO(), 4, 4) = 0xFAFBFCFD
BEGIN
INSERT INTO dbo.tableB ( ... )
SELECT ...
END /*IF*/
The actual DELETE code:
SET CONTEXT_INFO 0x000000FAFBFCFD;
DELETE FROM dbo.tableA WHERE ...
SET CONTEXT_INFO 0x00;

Again, that defeats the whole purpose of the audit table to begin with. If you test in prod, you need to audit those tests. If nothing else, it will keep the auditors from installing handrails on your hinny during an audit. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101076 More actions · Answer 10

Jeff Moden - Thursday, July 5, 2018 8:51 AM
ScottPletcher - Thursday, July 5, 2018 8:24 AM
To answer the original q, yes, there is a very easy way to "tell" the trigger not to do the INSERTs into table B.
Use specific byte(s) of CONTEXT_INFO to control it. Set them to the special value before you run the DELETE to cancel INSERTs to B:
--in the DELETE trigger code itself
CREATE TRIGGER ...
...
IF NOT SUBSTRING(CONTEXT_INFO(), 4, 4) = 0xFAFBFCFD
BEGIN
INSERT INTO dbo.tableB ( ... )
SELECT ...
END /*IF*/
The actual DELETE code:
SET CONTEXT_INFO 0x000000FAFBFCFD;
DELETE FROM dbo.tableA WHERE ...
SET CONTEXT_INFO 0x00;
Again, that defeats the whole purpose of the audit table to begin with. If you test in prod, you need to audit those tests. If nothing else, it will keep the auditors from installing handrails on your hinny during an audit. 😉

It may or may not. It depends on the true purpose of the audit table. If you need a truly unbreakable audit trail, you can't use just a SQL trigger for that anyway. You need separate hardware and/or processes that do the logging outside of db control.

By the comments above, the op has the authority to delete from the audit table anyway, so we're not dealing with a super-secure audit process here. Given that, it's much more efficient to just avoid doing the inserts rather than deleting them afterwards, as the op noted.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736068 More actions · Answer 11

I'd lean towards using some table that tracks test data and then using that in reports (or views) to ensure the test data isn't included in the reporting system.

I've seen people keep test data in prod systems, often because they want to check something in prod without affecting actual data. This allows them a real system test that isn't possible otherwise, like with partners. We had "test" accounts in financial trading systems that allowed us to simulate trades and submit them, but the partner wouldn't execute them.

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 12

ScottPletcher - Thursday, July 5, 2018 9:07 AM
Jeff Moden - Thursday, July 5, 2018 8:51 AM
ScottPletcher - Thursday, July 5, 2018 8:24 AM
To answer the original q, yes, there is a very easy way to "tell" the trigger not to do the INSERTs into table B.
Use specific byte(s) of CONTEXT_INFO to control it. Set them to the special value before you run the DELETE to cancel INSERTs to B:
--in the DELETE trigger code itself
CREATE TRIGGER ...
...
IF NOT SUBSTRING(CONTEXT_INFO(), 4, 4) = 0xFAFBFCFD
BEGIN
INSERT INTO dbo.tableB ( ... )
SELECT ...
END /*IF*/
The actual DELETE code:
SET CONTEXT_INFO 0x000000FAFBFCFD;
DELETE FROM dbo.tableA WHERE ...
SET CONTEXT_INFO 0x00;
Again, that defeats the whole purpose of the audit table to begin with. If you test in prod, you need to audit those tests. If nothing else, it will keep the auditors from installing handrails on your hinny during an audit. 😉
It may or may not. It depends on the true purpose of the audit table. If you need a truly unbreakable audit trail, you can't use just a SQL trigger for that anyway. You need separate hardware and/or processes that do the logging outside of db control.
By the comments above, the op has the authority to delete from the audit table anyway, so we're not dealing with a super-secure audit process here. Given that, it's much more efficient to just avoid doing the inserts rather than deleting them afterwards, as the op noted.

Totally on the "truly unbreakable" thing. That, not withstanding, if you are authorized to delete from the audit tables, the auditors will have a field day with you one way or another. Of course, they'll probably also have a field day with people testing in production to begin with. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 13

Cheers Scott. This is probably technically was I was originally after & not something I'm familiar with so thank you. I'll have a read up and a play.

Steve - absolutely. Further to all of these responses that's what I'm working on. I needed to step back from the code and this post made me do that.

WRT test data on prod generally - we know how we should work & would choose to work but I'm freelance and fairly small fry. I'm paid to get it done & keep the system up and as responsive as possible. I can advise on best practice sure, but with my kind of clients, the budget & often the infrastructure is decided long before me. There are 3rd parties involved each with their own agendas (& budgets & timescales & egos) and getting agreement for larger scale changes is extremely challenging. Sometimes I just need to be pragmatic & find a technical way to very carefully get it sorted.

This post has been super helpful to me. Thank you to everyone.