Disable Trigger ONLY For My Insert?

Oct 1, 2007



Howdy all. Got a q someone might know something about.

Assume TableA and TableB, identical structure. TableA has an insert trigger.

I want to insert the contents of B into A, but i do NOT want the trigger to fire for my particular insert. During the insert (might be millions of records coping from B to A) i would like the trigger to continue to fire for anyone else that inserts data.

My research suggests that I need to BCP out from B to a file, then BULK INSERT from the file back to A. Anyone have any other ideas?

This was my first thoght, but of course, this will disable the trigger for ALL insert operations, not just mine:



Code Block
DISABLE MyInsertTrigger on TableA;
GO
insert into TableA
select * from TableB
GO
ENABLE MyInsertTrigger on TableA;
GO

View 3 Replies


ADVERTISEMENT

Disabilitazione Trigger [DISABLE TRIGGER]

Jul 20, 2005

Salve, non riesco a disabilitare un trigger su sqlserver nè da queryanalyzer, nè da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.

View 4 Replies View Related

Can We Disable A Trigger In SQL 7.0

Jun 14, 2002

Is there a way to disable trigger for a time window and enable it when desired.

View 1 Replies View Related

Disable Trigger

Jul 12, 2001

Is it possible to disable a trigger to manually manipulate data without completely removing it? Thank you.

View 2 Replies View Related

Disable Trigger

May 11, 2001

Is it possible to disable a trigger in SQL Server 6.5?
Is so, how can i do that?

View 1 Replies View Related

Disable Trigger

Oct 25, 2004

Hi,

can I disable a trigger in Sqlserver 2000??? When i run a store procedure who works with one table i want that the trigger doesn´t work it. After that the trigger would be enabled again.

I know i can delete it and create it again but something like "ALTER TRIGGER DISABLED" would be ok.

Thanks.

View 6 Replies View Related

Disable Trigger

Mar 23, 2004

Sorry, may be it is very simple, but
how can I disable all trigger on a tabled under sql 2000

View 3 Replies View Related

Trigger Disable

Aug 9, 2007

Sorry to reopen a post, but I'm having the same problem. Worse, I can't change the trigger code right now.

My concerns with disabling triggers are:

1) DISABLE TRIGGER affects all sessions, not just the session doing the mass load
2) DISABLE TRIGGER is permanant until re-enabled, so if the mass load process fails (and if our TRY/CATCH blocks aren't perfect), then the triggers would remain disabled for normal OLTP use.
Any ideas on ways around this? I can identify all the work the triggers would have done and do it on my own. The problem is getting them not to do it!

View 9 Replies View Related

Disable Specific Trigger

Jul 26, 2004

I have a dilema, I need to have a delete trigger enabled to track user deletes to update an external history table. However, when the posting process runs for the table for which the delete trigger runs, all the records from that table are deleted. Th end result is that instead of capturing the specific deletes, it shows all recods being deleted. I know you can disable foreign keys and triggers as a whole, can you do it for specific triggers?

View 1 Replies View Related

How To Disable A Trigger In Duration?

Feb 21, 2005

Hello, everyone:

There is a trigger to monitor the modification on a table, and it turn on. For a special duration, I need to turn off this trigger to modify the table. And then turn on the trigger again.

Any help will be appreciated.

Thanks

ZYT

View 2 Replies View Related

Disable Trigger Syntax?

Sep 4, 2007





Code Snippet
CREATE PROCEDURE Staff_NoteGroup_insert
(

@staffID AS int,
@owner AS int,
@subject AS varchar(256),
@message AS varchar(512),
@date_add AS DateTime
)
AS
BEGIN TRANSACTION SERIALIZABLE

DECLARE @id as int
SELECT @id = (SELECT MAX(id) FROM Staff_NoteGroup) + 1

IF @id IS NULL

SET @id = 1

INSERT INTO Staff_NoteGroup VALUES(@id, @staffID, @owner, @subject, GETDATE(), GETDATE())

IF @@error <> 0 BEGIN

ROLLBACK
RETURN
END

DISABLE TRIGGER Staff_Note_insert ON DATABASE
INSERT INTO Staff_Note VALUES(1, @id, @owner, @message, @date_add)
ENABLE TRIGGER Staff_Note_insert ON DATABASE

IF @@error <> 0 BEGIN

ROLLBACK
RETURN
END
COMMIT
GO






Below is the error message.


Msg 156, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 26

Incorrect syntax near the keyword 'TRIGGER'.

Msg 102, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 28

Incorrect syntax near 'ENABLE'.

Msg 102, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 33

Incorrect syntax near 'COMMIT'.


Where is the problem?
I'm using sql server 2005

Thanks,
Max

View 6 Replies View Related

How To Disable LOGON TRIGGER ?

May 25, 2007

SQL Server 2005 SP2 add LOGON TRIGGER feature,

but, if logon trigger have bug, it seems that anyone can connect to the sql server.



for example:

create trigger tr_logon1_bug

to all server

for logon

as

insert into t1 values(original_name(), getdate());



and, drop table 't1'.



the result, everyone fail to logon, even if sa. (may be..)

therefore, cannot disable/drop this trigger ...



So, Are there anyway to disable logon trigger,

or to connect by sa/Admin,

when logon trigger failed.



Regards,

View 3 Replies View Related

SQL 2012 :: Disable And Enable Trigger?

May 25, 2015

I have a trigger that I disable so I can do some operations on a table.

After the operations are completed I do the enable using the enable trigger comand.

Do I need to do something else to put back the trigger working as it was before the disable comand?

View 3 Replies View Related

Can I Disable Trigger For A Single Sproc Only?

Jun 9, 2008

Hi

So, I know, after searching these forums, that it is possible to disable a trigger before updating a table and then enabling it again afterwords, for instance, from a stored procedure.

I might be dealing with hypotheticals here, but when I do a ...

ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }

... from a stored procedure, will it not be database wide?
Should I worry about another change to the table happening in the timespan in which the trigger is disabled (which it would be for during a single update), which the trigger should have caught?

Regards, Egil.

View 1 Replies View Related

Trigger On Enable / Disable SQL User

Mar 31, 2006

Is it possible to run a trigger whenever a SQL user is disabled or enabled? From what I've seen of various sysusers and syslogins tables there isn't a column that represents enabled or disabled.

View 6 Replies View Related

Disable/Enable A Trigger On A Replicated Table.

Apr 9, 2002

I have transaction replication setup on two SQL7 boxes and a nightly job runs a procedure that need to alter a table and disable a trigger. Since replication has been set up the disable doesn't work. Any way around this.

View 2 Replies View Related

SQL Server 2012 :: Disable Only One Trigger Action?

May 6, 2014

I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only the "AFTER DELETE", letting AFTER INSERT, UPDATE act normally?

View 6 Replies View Related

SQL Server 2008 :: Enable And Disable Trigger

Oct 25, 2015

the disable trigger will be enable again.Because I already disable the trigger on last month, but when I check through the database, it enable again.I understand that, when we restore the database, all the trigger will be enable.How about SQL Cluster? will it enable the trigger??

View 3 Replies View Related

SQL2000: Disable Trigger Not Allowed In Tables That Involved In Publication.

Apr 10, 2007

We have setup a replication in SQL2000:

We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command

Alter table 'tbl' disable trigger 'abc' errored out. The error message said:

''Cannot alter the table 'tbl' because it is being published for replication."

I've digged more into this and found although it's not allowed to disable a triggers,

the SQLServer do allow delete the trigger and recreate them.

Is there any way to disable the trigger directly?

Thanks in advance,

Don



BTW:

I've used the following sql directly, however the trigger still fires.

UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@table_name)



The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.



View 8 Replies View Related

SQL 2012 :: SSDT Database Project - Cross DB Trigger Enable / Disable Gives Warning 71502

Jul 13, 2015

I have 2 dbs (SQL 2012) - one contains a trigger that is enabled/disabled by a procedure in the other database. This all works fine.

If I create a Database Project solution in Visual Studio 2012 SSDT (or 2013) for both databases, the stored procedure generates a SQL71502 stating that my trigger name can't be resolved.

To recreate the issue:

CREATE DATABASE DbWithTrigger
GO
USE DbWithTrigger
GO
CREATE TABLE dbo.TblWithTrigger(
Id int NULL,
SomeValue varchar(30) NULL

[code]....

-- Test to confirm

EXEC CrossDbTriggerCall
INSERT DbWithTrigger.dbo.TblWithTrigger VALUES(1, 'Blah blah')

In Visual Studio 2012:

1. Create a new solution with a project named DbWithTrigger
2. In project settings set the Target platform to SQL 2012
2. Import the DbWithTrigger db into this project
3. Create a new project named DbCallsTrigger
4. In project settings set the Target platform to SQL 2012
5. Import the DbCallsTrigger db into this project
6. Add a Database Reference in DbCallsTrigger for DbWithTrigger

When you build the solution both dbs build successfully, however there are two warnings. One is easily resolved by replacing DbWithTrigger in the body of the procedure with [$(DbWithTrigger)] (db variable name for the reference) but I can't find out how to get rid of the other. Is it a bug?

View 1 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Oct 30, 2007

Table 1





First_Name

Middle_Name

Surname


John

Ian

Lennon


Mike

Buffalo

Tyson


Tom

Finney

Jones

Table 2




ID

F

M

S

DOB


1

Athony

Harold

Wilson

24/4/67


2

Margaret

Betty

Thathcer

1/1/1808


3

John

Ian

Lennon

2/2/1979


4

Mike

Buffalo

Tyson

3/4/04


5

Tom

Finney

Jones

1/1/2000


I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.

View 17 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Feb 5, 2008

A





ID

Name


1

Joe


2

Fred


3

Ian


4

Bill


B





ID


1


4

I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?

I believe the trigger should look something like this:

create trigger test_trigger
on a
for insert
as
insert into b(ID)

select i.id
from inserted i
where
--specific USER

View 9 Replies View Related

Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement

Jan 14, 2008


This problem is being seen on SQL 2005 SP2 + cumulative update 4

I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable

I now need to add an "instead of insert" trigger to the table that is the subject of the insert.

As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows

Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table

Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.

To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row

I need the behaviour to be correct when the trigger is present

Any thoughts would be much appreciated

aero1


/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)

GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted

END

/************************************************
3) - Do the insert
************************************************/

DECLARE @my_insert TABLE( my_table_id bigint )

declare @forename VARCHAR(100)
declare @surname VARCHAR(50)

set @forename = N'john'
set @surname = N'smith'

INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)

select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger

/************************************************
4) - Drop the trigger
************************************************/

drop trigger [dbo].[trig_my_table__instead_insert]
go

/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK

View 5 Replies View Related

Multiple Insert Call For A Table Having Insert Trigger

Mar 1, 2004

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

Thanks

View 10 Replies View Related

Insert Trigger For Bulk Insert

Nov 25, 2006

In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,

View 1 Replies View Related

Insert Trigger - How To

Nov 20, 2006

 I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have  some similar sample code?
Thank you very much

View 1 Replies View Related

Insert Trigger

Dec 3, 2006

I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have  some similar sample code?
Thank you very much

View 1 Replies View Related

Insert Trigger

Nov 12, 2001

Should a insert trigger fire my update trigger as well? it fires it automatically after the insert? Is this a bug?

View 1 Replies View Related

Insert Trigger (Help)

Aug 31, 2000

I have to create a insert trigger that calls a stored procedure usp_a
I need to pass two parameters to this stored procedure, value coming from the row inserted...
What is the syntax? Any help will be appreciated?

View 1 Replies View Related

Insert Trigger Help

Jan 14, 2005

Hi.

Replication is not an option for me so I am trying to creat a trigger that will basically copy any new record to another database. I am using an on insert trigger and I get all records from the table inserted to the new db not just the new record.

Any ideas?

Thanks.

Bill

View 4 Replies View Related

Pre-insert Trigger

Apr 26, 1999

I am trying to write a pre-insert trigger. I want a row to be deleted first and then have the new row inserted. The end result is an insert/update statement. If anyone knows how to do this or has a better way, let me know, Please.

View 3 Replies View Related

Insert Trigger

Oct 13, 1998

I am trying to write an insert trigger for the following problem. I have a table that contains a field (amount) that contains currency amount. Each row could be a record from a transaction in a different country. I would like each record to be converted into U.S. currency as it is inserted. So I created an other table that contains that exchange rate. How can I create a trigger that does this?

Tax Detail Table Exchange Table

Account# int Country char
Description char ExchangeRate Money
Amount money
Country char

I am working with MSS 6.5. Any and all help will be greatly appreciated.

Fidencio Peña

View 3 Replies View Related

ON INSERT TRIGGER

Dec 8, 1998

MSSQL 6.5

Is there any way to change some columns on inserted lines in ON INSERT trigger?

My notice: I try to find something about it in SQLBOOKS, but I found nothing.
It's possibly not TRUEEE!!!!! It wants me to grumble.


Thank for your answer.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved