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
ADVERTISEMENT
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
Jun 21, 2007
I am writing an adapter for a two way integration between two databases. The table I am currently working on has an update and insert trigger to send data to the other database via service broker.
Asd I insert/update data via my adapter I don't want the triggers to fire but I do want them to fire if somebody else make data modifications while I am working with the table.
I am doing all mu modifications within a transaction but I found I couldn't disable the trigger within a stored procedure to keep the disable and enable as close together as possible.
Does anybody ahve any ideas how to overcome this scenario or am I worrying about nothing as my transaction will keep other users from modifying the data until I've finished.
Any advice would be great.
Cheers
View 4 Replies
View Related
Jun 14, 2002
Is there a way to disable trigger for a time window and enable it when desired.
View 1 Replies
View Related
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
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
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
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
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
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
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
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
View Related
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
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
Jun 18, 2008
Hi. Apologies if this has come up before, I wonder if someone can help.
I want to call a sproc from a trigger, passing in the value of a field from the updated/inserted/deleted row.
It would be like spUpdateUser @UserID = x
where x is the value from one of the columns of updated row.
What is the syntax for this please? TIA
View 8 Replies
View Related
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
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
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
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
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
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
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
Feb 10, 2014
I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like
CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS
[code]....
I m getting some syntactical errors.
View 6 Replies
View Related
Jan 16, 2015
I have this table:
CREATE TABLE Workspaces (
AreaNr CHAR(2)
CONSTRAINT ck_a_areanr REFERENCES Areas(AreaNr)
ON DELETE CASCADE
[Code] ....
Now I want to create a trigger that prevents a delete on a single row (randomly chosen) from the table Workspaces. At the moment I have the following trigger, but this trigger still allows removal of single rows.
Current trigger:
CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS
BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;
[Code] ....
Desired result: I want to be able to prevent a delete on a single row on the table above.
View 1 Replies
View Related
Apr 15, 2008
Hi all,
My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks
Create Trigger dbo.myTrigger
ON dbo.[myTable]
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()
BEGIN TRY
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',Charges,@now, suser_sname()
FROM INSERTED
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
View 3 Replies
View Related
Jan 17, 2008
Hi
Can u please send the answers for this
1 . Can write more than one trigger for a single table(sql server 2000)?
2. how to create the editable gridview? While clicking particular cell it should be changed to
Edit mode , and I want option for creating new row and delete option, search option
View 1 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Jan 30, 2015
I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),
[code]....
View 0 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Jan 25, 2008
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID.
What I have tried thus far:
UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID)
This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated.
Thanks!
Amanda
View 1 Replies
View Related
May 2, 2008
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something?
SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
View 4 Replies
View Related
