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.
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 RelatedIs there a way to disable trigger for a time window and enable it when desired.
View 1 Replies View RelatedIs it possible to disable a trigger to manually manipulate data without completely removing it? Thank you.
View 2 Replies View RelatedIs it possible to disable a trigger in SQL Server 6.5?
Is so, how can i do that?
Sorry, may be it is very simple, but
how can I disable all trigger on a tabled under sql 2000
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!
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 RelatedHello, 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
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
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
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,
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?
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.
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 RelatedI 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 RelatedI 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 Relatedthe 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 RelatedWe 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.
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?
Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies View RelatedThis Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
A quick question, how can I disable identity for the identity column
without dropping that column? Thanks!
Xiao
Is there a way to disable logging ?
I'm about to split a 1000000 rows table into three other tables , and would prefere not to log this action ...
Does anyone know how to do it in a SQL 7.0 statement ?
I don't seem to be able to find a command to disable triggers. Can this
be done in SQL7?
Thanks.
read-only option
dbo-use option
...
Anyone any ideas ?!?
CDC is disabling when we do bulkinsertsupdateschange schema .how to findout why CDC disabled and how to prevent it ?
View 3 Replies View RelatedHi,
Is there any way to disable the primary key constraint?.I want to move the records from one table to another.say column 'x' in the source table doesnt contain a primay key.But my target table column 'x' contains primay key.
How to ovecome this problem?
Thanks in advance
Hello all.
Could anyone advise me how to disable indexes on a specific table at the start of a script, then re-enable them at the end?
Thanks all.
I am looking for a way to disable DatabaseMail90.exe (temporarily) while still allowing emails to queue up.
At first, I thought the solution was sysmail_stop_sp... but if I use this... calls to sp_send_DBMail fail with the following message:
Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.
I'd like the emails to be queued... I just want to postpone sending them. Anyone know how to do this?
- Alan D. Nelson
In meen. primary keys, NOT NULL, IDENTETIES...et.cI have to do a maunally, one time, building of a database. Sometables has tostay an some are to be exchanged. The foreignkey inforcemnt ill do for myself so everything is correct. I just need to be allowed to de thede taskfor a while. Is it impossible?RegardsAnders
View 1 Replies View RelatedI am trying to disable certain users, but it is giving me this error. I am able to disable some accounts, but not all of them. It seems to happen with user that have been recently created.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Disable Login. failed for Login 'domainuser'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Disable+Login.+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain information about Windows NT group/user 'domainuser', error code 0x5. (Microsoft SQL Server, Error: 15404)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15404&LinkId=20476
The links lead to a page that is no longer available.
I read somewhere that it had to do with the SQL Server service account and that if you change it to a Domain Administrator it would and it does, but I can not believe the solution. I would say that this even started happening after we applied SQL 2005 Service Pack 2.
Has anyone else seen this problem. Do I really need to make it a Domain Administrator?
Thanks