I've 2 Windows 2000 server running each own instance of SQL2000. I've setup
both linked servers @. both end.
At server A, it'll call a sp in server B, whereby this sp will update server
B tables based on server A's data. And the server A table A will trigger
back to server B.
However due to some business logic and distributed transaction don't allow a
loopback operation, is there any method the trigger can be bypassed?
I can't drop this trigger as it need to be maintained when other process
access this table.
My question is, can a disable/enable trigger be issued in this sp?
What if another process updating this table at the same time and need the
trigger to be enabled? Will this process be queued after this completion of
sp?
Please advice on how feasible can trigger be controlled from firing..
Server_A call ServerB.sp
|
V
store procedure @. serverB
begin transaction
-- disable trigger
insert into serverB.dbB.dbo.tableB
select * from serverA.dbA.dbo.tableA where key=X
commit transaction
-- enable trigger
|
V
serverB.dbB.dbo.tableB trigger back to serverA
if record not found in serverA
insert into serverA
else
update into server A
Thanks in advance
KristeHi
You can do an ALTER TABLE with the ENABLE/DISABLE TRIGGER, but, the
disabling is for all connections.
The only way you could do this is to have the trigger look up in a table
what connections it can fire for. Not elegant, but might be workable.
Regards
Mike
"Kriste L" wrote:
> I've 2 Windows 2000 server running each own instance of SQL2000. I've setu
p
> both linked servers @. both end.
> At server A, it'll call a sp in server B, whereby this sp will update serv
er
> B tables based on server A's data. And the server A table A will trigger
> back to server B.
> However due to some business logic and distributed transaction don't allow
a
> loopback operation, is there any method the trigger can be bypassed?
> I can't drop this trigger as it need to be maintained when other process
> access this table.
> My question is, can a disable/enable trigger be issued in this sp?
> What if another process updating this table at the same time and need the
> trigger to be enabled? Will this process be queued after this completion o
f
> sp?
> Please advice on how feasible can trigger be controlled from firing..
> Server_A call ServerB.sp
> |
> V
> store procedure @. serverB
> begin transaction
> -- disable trigger
> insert into serverB.dbB.dbo.tableB
> select * from serverA.dbA.dbo.tableA where key=X
> commit transaction
> -- enable trigger
>
> |
> V
> serverB.dbB.dbo.tableB trigger back to serverA
> if record not found in serverA
> insert into serverA
> else
> update into server A
>
> Thanks in advance
> Kriste
>
>
2012年3月7日星期三
Disabling triggers with alter table, execute as?
I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Disabling triggers with alter table, execute as?
I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Disabling triggers from stored procedures
Hi,
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
FedericoALTER TABLE [MyTable] DISABLE TRIGGER trMyTrigger|||Just curious as to why you would want to do this?|||Yes, it is possible to disable the trigger, but keep in mind that you are disabling that trigger for everyone using the table, not just for what the trigger is doing. I don't know of any way to allow a trigger to function for some users/spids and not for others, except to include code within the trigger to make it decide that it shouldn't run under some conditions.
-PatP|||If SQL Server 2000 and one can alter the trigger then one can use SET CONTEXT_INFO in SP and then check master..sysprocesses from trigger.
Might also insert DBCC INPUTBUFFER(@.@.SPID) results into temporary table in trigger to check for proc name in EventInfo column. Would work in SQL Server 7 and would not require any additional code in SP, but would hurt trigger performance, be kludgy, and require hard-coding proc name(s) in trigger.
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
FedericoALTER TABLE [MyTable] DISABLE TRIGGER trMyTrigger|||Just curious as to why you would want to do this?|||Yes, it is possible to disable the trigger, but keep in mind that you are disabling that trigger for everyone using the table, not just for what the trigger is doing. I don't know of any way to allow a trigger to function for some users/spids and not for others, except to include code within the trigger to make it decide that it shouldn't run under some conditions.
-PatP|||If SQL Server 2000 and one can alter the trigger then one can use SET CONTEXT_INFO in SP and then check master..sysprocesses from trigger.
Might also insert DBCC INPUTBUFFER(@.@.SPID) results into temporary table in trigger to check for proc name in EventInfo column. Would work in SQL Server 7 and would not require any additional code in SP, but would hurt trigger performance, be kludgy, and require hard-coding proc name(s) in trigger.
Disabling triggers
Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
Disabling triggers
Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
Disabling Trigger within a trigger?
Hi all,
I would like to disable a trigger within a trigger using...
ALTER TABLE RESIDENTIAL DISABLE TRIGGER UPDATE_SYSTEMS_RES
However it tells me that I cant disable a trigger within one. How can I get around this or is there another way of disabling a trigger.
Thanks
AnthonyYou will need to alter the code of your trigger to test if the trigger should do it's thing or bail. To my knowledge there is no way to disable a trigger from fireing while inside a trigger.
I would like to disable a trigger within a trigger using...
ALTER TABLE RESIDENTIAL DISABLE TRIGGER UPDATE_SYSTEMS_RES
However it tells me that I cant disable a trigger within one. How can I get around this or is there another way of disabling a trigger.
Thanks
AnthonyYou will need to alter the code of your trigger to test if the trigger should do it's thing or bail. To my knowledge there is no way to disable a trigger from fireing while inside a trigger.
2012年2月25日星期六
Disabling Foreign Key Constraint in Trigger
Hey,
Can anybody tell me how to disable a Foreign Key contraint from executing
while a Trigger is executing on DELETE event?
Best regards,
BillDRI are checked berofe the AFTER TRIGGERS, so if it is a problem the trigger
will not be fired.
AMB
"AST" wrote:
> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>
>|||Hey AMB,
The table [HistoryDetails] has a FK on the column HistoryID that is being
used to enforce integrity with the table[History].
I am not sure if you are stating you can't disable the FK or if I have to
modify the context of the Trigger?
Best regards,
Bill
The Trigger is defined as:
/****** Object: Trigger dbo.TRIGGER_CascDeleteOnHistoryDetails Script
Date: 5/24/2002 12:14:53 AM ******/
CREATE TRIGGER [TRIGGER_CascDeleteOnHistoryDetails] ON dbo.History
FOR DELETE
AS
DELETE HistoryDetails FROM deleted, HistoryDetails WHERE deleted.HistoryID =
HistoryDetails.HistoryID
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:4662B0B5-25C5-4217-84F0-B75E5ECCF604@.microsoft.com...
> DRI are checked berofe the AFTER TRIGGERS, so if it is a problem the
trigger
> will not be fired.
>
> AMB
>
> "AST" wrote:
>
executing|||That will make quite a mess:
1) Disabling a Foreign Key constraint is Data Definition Language, and can
only be executed by the table owner and members of the sy
min fixed server
roles and the db_owner and db_ddladmin database roles, and is not
transferable. So you have to give your users a large number of unnecessary
permissions.
2) Disabling a foreign key works for all connections, not just the current
one. So while you disable it in your trigger, someone else might need it.
What exactly are you trying to achieve? This is definitely not the right
way.
Jacco Schalkwijk
SQL Server MVP
"AST" <no_reply@.please.com> wrote in message
news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>|||Hey Jacco,
What I am trying to achieve is the following:
I have 2 tables [History], [HistoryDetails] where the Primary Table
[History] mantains detailed data in the [HistoryDetails] table. The key
relationship is History.HistoryID=> HistoryDetails.HistoryID.
I have place a constraint on the [HistoryDetails] table such that a record
cannot be added unless the HistoryID is existing in the [History] table.
I had also created a trigger to delete all relational records in the
[HistoryDetails] table when the Primary Key record in the [History] table
was deleted.
This is where I run into problems because the execution of the trigger fails
due to the FK Constraint.
Any suggestions would be very welcome?
Best regards,
Bill
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uEUZEs6EFHA.2700@.TK2MSFTNGP14.phx.gbl...
> That will make quite a mess:
> 1) Disabling a Foreign Key constraint is Data Definition Language, and can
> only be executed by the table owner and members of the symin fixed
server
> roles and the db_owner and db_ddladmin database roles, and is not
> transferable. So you have to give your users a large number of unnecessary
> permissions.
> 2) Disabling a foreign key works for all connections, not just the current
> one. So while you disable it in your trigger, someone else might need it.
> What exactly are you trying to achieve? This is definitely not the right
> way.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AST" <no_reply@.please.com> wrote in message
> news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
executing
>|||Drop the trigger and use the ON DELETE CASCADE option on the foreign
key constraint.
David Portas
SQL Server MVP
--|||Hey David,
I don't think this is supported in SQL 7 and I have to support both SQL7 and
SQL2000
I tried running the SQL to add this constraint in SQL7 and it fails and
doesn't seem to recognize anthing to do with cascaded deletes?
Best regards,
Bill
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
> Drop the trigger and use the ON DELETE CASCADE option on the foreign
> key constraint.
> --
> David Portas
> SQL Server MVP
> --
>|||AST
Well, you have to drop a FK constraint and to allow deletion within a
trigger.
"AST" <no_reply@.please.com> wrote in message
news:emTg1W8EFHA.624@.TK2MSFTNGP09.phx.gbl...
> Hey David,
> I don't think this is supported in SQL 7 and I have to support both SQL7
and
> SQL2000
> I tried running the SQL to add this constraint in SQL7 and it fails and
> doesn't seem to recognize anthing to do with cascaded deletes?
> Best regards,
> Bill
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
>
Can anybody tell me how to disable a Foreign Key contraint from executing
while a Trigger is executing on DELETE event?
Best regards,
BillDRI are checked berofe the AFTER TRIGGERS, so if it is a problem the trigger
will not be fired.
AMB
"AST" wrote:
> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>
>|||Hey AMB,
The table [HistoryDetails] has a FK on the column HistoryID that is being
used to enforce integrity with the table[History].
I am not sure if you are stating you can't disable the FK or if I have to
modify the context of the Trigger?
Best regards,
Bill
The Trigger is defined as:
/****** Object: Trigger dbo.TRIGGER_CascDeleteOnHistoryDetails Script
Date: 5/24/2002 12:14:53 AM ******/
CREATE TRIGGER [TRIGGER_CascDeleteOnHistoryDetails] ON dbo.History
FOR DELETE
AS
DELETE HistoryDetails FROM deleted, HistoryDetails WHERE deleted.HistoryID =
HistoryDetails.HistoryID
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:4662B0B5-25C5-4217-84F0-B75E5ECCF604@.microsoft.com...
> DRI are checked berofe the AFTER TRIGGERS, so if it is a problem the
trigger
> will not be fired.
>
> AMB
>
> "AST" wrote:
>
executing|||That will make quite a mess:
1) Disabling a Foreign Key constraint is Data Definition Language, and can
only be executed by the table owner and members of the sy
min fixed serverroles and the db_owner and db_ddladmin database roles, and is not
transferable. So you have to give your users a large number of unnecessary
permissions.
2) Disabling a foreign key works for all connections, not just the current
one. So while you disable it in your trigger, someone else might need it.
What exactly are you trying to achieve? This is definitely not the right
way.
Jacco Schalkwijk
SQL Server MVP
"AST" <no_reply@.please.com> wrote in message
news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>|||Hey Jacco,
What I am trying to achieve is the following:
I have 2 tables [History], [HistoryDetails] where the Primary Table
[History] mantains detailed data in the [HistoryDetails] table. The key
relationship is History.HistoryID=> HistoryDetails.HistoryID.
I have place a constraint on the [HistoryDetails] table such that a record
cannot be added unless the HistoryID is existing in the [History] table.
I had also created a trigger to delete all relational records in the
[HistoryDetails] table when the Primary Key record in the [History] table
was deleted.
This is where I run into problems because the execution of the trigger fails
due to the FK Constraint.
Any suggestions would be very welcome?
Best regards,
Bill
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uEUZEs6EFHA.2700@.TK2MSFTNGP14.phx.gbl...
> That will make quite a mess:
> 1) Disabling a Foreign Key constraint is Data Definition Language, and can
> only be executed by the table owner and members of the sy
min fixedserver
> roles and the db_owner and db_ddladmin database roles, and is not
> transferable. So you have to give your users a large number of unnecessary
> permissions.
> 2) Disabling a foreign key works for all connections, not just the current
> one. So while you disable it in your trigger, someone else might need it.
> What exactly are you trying to achieve? This is definitely not the right
> way.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AST" <no_reply@.please.com> wrote in message
> news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
executing
>|||Drop the trigger and use the ON DELETE CASCADE option on the foreign
key constraint.
David Portas
SQL Server MVP
--|||Hey David,
I don't think this is supported in SQL 7 and I have to support both SQL7 and
SQL2000
I tried running the SQL to add this constraint in SQL7 and it fails and
doesn't seem to recognize anthing to do with cascaded deletes?
Best regards,
Bill
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
> Drop the trigger and use the ON DELETE CASCADE option on the foreign
> key constraint.
> --
> David Portas
> SQL Server MVP
> --
>|||AST
Well, you have to drop a FK constraint and to allow deletion within a
trigger.
"AST" <no_reply@.please.com> wrote in message
news:emTg1W8EFHA.624@.TK2MSFTNGP09.phx.gbl...
> Hey David,
> I don't think this is supported in SQL 7 and I have to support both SQL7
and
> SQL2000
> I tried running the SQL to add this constraint in SQL7 and it fails and
> doesn't seem to recognize anthing to do with cascaded deletes?
> Best regards,
> Bill
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
>
2012年2月24日星期五
Disable triggers only for active sessions
Hey,
is it possible to disable all DML triggers for the current session only?
This is for manual data correction sessions where no trigger should be fired but all other user connection still should have active triggers.
Cheers,I don't think you can do this without modifying your trigger. You can use SUSER_NAME() function inside your trigger and if it matches with the login user bypass the execution.
to be specific.
The first line of trigger code will be if SUSER_NAME() = 'Manuel' return|||I can second that, you will have to implement some additional logic to make this possible.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Disable triggers for a single update
Hello,
I have a trigger on a table (after update) which keeps track of modified
records / columns. I need to have a function wich can make updates to that
table without firing the trigger (i.e. I don't want to track changes made by
that function).
So, I need is to make the following statements "atomic" (otherwise I could
miss other updates I want to keep tracking)
- alter table disable trigger
- update table
- alter table enable trigger.
The first option I considered was using a lock on the whole table, which i'd
like to avoid due to the usual performance issues. Now I'm thinking about
using a transaction. I know that transactions ensure actions are atomic with
respecto to recovery, I'd like to know if they can solve this kind of issues
too.
Thank you,
Luca> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
Wrapping the ALTER/UPDATE/ALTER in a transaction will ensure the operation
is atomic. However, the ALTER will acquire a Sch-M lock on the table so no
other users can access the table for the duration of the transaction.
If this is the sort of thing you do often, consider including code in your
trigger to conditionally bypass the update. This will improve concurrency
and provide an atomic transaction as well. The example below uses a temp
table existence check:
CREATE TRIGGER TR_MyTable
ON MyTable FOR UPDATE
AS
IF OBJECT_ID('tempdb..#BypassTrigger') IS NULL
BEGIN
UPDATE MyTable
SET MyAuditDate = CURRENT_TIMESTAMP
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE MyTable.MyPK = inserted.MyPK
)
END
GO
You can then skip the trigger code for the current session by creating the
dummy temp table beforehand:
CREATE TABLE #BypassTrigger(Col1 int)
UPDATE MyTable
SET MyData = 'test'
WHERE MyPk = 1
DROP TABLE #BypassTrigger
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Luca" <luca@.none.net> wrote in message
news:Z3WXd.639929$b5.29135411@.news3.tin.it...
> Hello,
> I have a trigger on a table (after update) which keeps track of modified
> records / columns. I need to have a function wich can make updates to that
> table without firing the trigger (i.e. I don't want to track changes made
> by that function).
> So, I need is to make the following statements "atomic" (otherwise I could
> miss other updates I want to keep tracking)
> - alter table disable trigger
> - update table
> - alter table enable trigger.
> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
> Thank you,
> Luca
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ha scritto nel messaggio
news:eqTc$AXJFHA.2764@.tk2msftngp13.phx.gbl...
> --
> Hope this helps.
>
This was really helpful, thanks a lot.
Luca
I have a trigger on a table (after update) which keeps track of modified
records / columns. I need to have a function wich can make updates to that
table without firing the trigger (i.e. I don't want to track changes made by
that function).
So, I need is to make the following statements "atomic" (otherwise I could
miss other updates I want to keep tracking)
- alter table disable trigger
- update table
- alter table enable trigger.
The first option I considered was using a lock on the whole table, which i'd
like to avoid due to the usual performance issues. Now I'm thinking about
using a transaction. I know that transactions ensure actions are atomic with
respecto to recovery, I'd like to know if they can solve this kind of issues
too.
Thank you,
Luca> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
Wrapping the ALTER/UPDATE/ALTER in a transaction will ensure the operation
is atomic. However, the ALTER will acquire a Sch-M lock on the table so no
other users can access the table for the duration of the transaction.
If this is the sort of thing you do often, consider including code in your
trigger to conditionally bypass the update. This will improve concurrency
and provide an atomic transaction as well. The example below uses a temp
table existence check:
CREATE TRIGGER TR_MyTable
ON MyTable FOR UPDATE
AS
IF OBJECT_ID('tempdb..#BypassTrigger') IS NULL
BEGIN
UPDATE MyTable
SET MyAuditDate = CURRENT_TIMESTAMP
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE MyTable.MyPK = inserted.MyPK
)
END
GO
You can then skip the trigger code for the current session by creating the
dummy temp table beforehand:
CREATE TABLE #BypassTrigger(Col1 int)
UPDATE MyTable
SET MyData = 'test'
WHERE MyPk = 1
DROP TABLE #BypassTrigger
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Luca" <luca@.none.net> wrote in message
news:Z3WXd.639929$b5.29135411@.news3.tin.it...
> Hello,
> I have a trigger on a table (after update) which keeps track of modified
> records / columns. I need to have a function wich can make updates to that
> table without firing the trigger (i.e. I don't want to track changes made
> by that function).
> So, I need is to make the following statements "atomic" (otherwise I could
> miss other updates I want to keep tracking)
> - alter table disable trigger
> - update table
> - alter table enable trigger.
> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
> Thank you,
> Luca
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ha scritto nel messaggio
news:eqTc$AXJFHA.2764@.tk2msftngp13.phx.gbl...
> --
> Hope this helps.
>
This was really helpful, thanks a lot.
Luca
disable trigger syntax?
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
To execute these commands, you will need to put semicolons before disable and enable:
;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
This will allow your code to compile. I don't know that this is a good idea, and it seems like you are not using it right (is Staff_Note_insert a database trigger? or is it a trigger on the Staff_Note table? That would be
;DISABLE TRIGGER Staff_Note_insert ON Staff_note)
But why disable it here? There might be a good reason, so I am not saying it is necessarily a bad thing, but at the very least if deserves a comment as to why you are doing this for other readers who might have to maintain this code later
|||
Aah the "ON DATABASE" part is suppose to be "ON Staff_Note", at first I thought I did not use the ON clause correctly, forgot to change it back.
As for the disabling it, The trigger will update Staff_NoteGroup.date_edit column, everytime a new note is added for the same group, and since this procedure is adding a new group which should have at least a single note, the date_edit should be empty.
I will probably remove the trigger later and replace them using procedure, currently adding the note is still using normal SQL, so I need a trigger to update the NoteGroup.
The database is originally an ms access so there is quite a lot of things to change to make use of the server facilities and also to make it safe for multi-user. I still have a lot to learn.
Regards,
Max
|||Just a quick thought on your code, and please keep in mind that this is just my opinion:I think it may be a better idea to make some sort of check IN the trigger that decides if you want to perform the function the trigger is doing as compared to disabling and re-enabling the trigger every time. I can't say specifically why, but the idea of disabling a trigger on a regular basis makes me uneasy.
Anyway, just my opinion.
dinsdale.
|||
>>I can't say specifically why, but the idea of disabling a trigger on a regular basis makes me uneasy. <<
No doubt!|||
Ah I have not thought about making a check in the trigger.
Why is it bad to disable and enable trigger every time? does it affect performance? data integrity?
This is the first time I use trigger (other than when I'm still a uni student.), so I'm not very experience with the side-effect.
Anyway, that trigger part is now gone, I remove the trigger completely!!, replaced by stored procedure.
My application is adapting to SQL Server quite well.
Regards,
Max
disable trigger syntax?
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
To execute these commands, you will need to put semicolons before disable and enable:
;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
This will allow your code to compile. I don't know that this is a good idea, and it seems like you are not using it right (is Staff_Note_insert a database trigger? or is it a trigger on the Staff_Note table? That would be
;DISABLE TRIGGER Staff_Note_insert ON Staff_note)
But why disable it here? There might be a good reason, so I am not saying it is necessarily a bad thing, but at the very least if deserves a comment as to why you are doing this for other readers who might have to maintain this code later
|||
Aah the "ON DATABASE" part is suppose to be "ON Staff_Note", at first I thought I did not use the ON clause correctly, forgot to change it back.
As for the disabling it, The trigger will update Staff_NoteGroup.date_edit column, everytime a new note is added for the same group, and since this procedure is adding a new group which should have at least a single note, the date_edit should be empty.
I will probably remove the trigger later and replace them using procedure, currently adding the note is still using normal SQL, so I need a trigger to update the NoteGroup.
The database is originally an ms access so there is quite a lot of things to change to make use of the server facilities and also to make it safe for multi-user. I still have a lot to learn.
Regards,
Max
|||Just a quick thought on your code, and please keep in mind that this is just my opinion:I think it may be a better idea to make some sort of check IN the trigger that decides if you want to perform the function the trigger is doing as compared to disabling and re-enabling the trigger every time. I can't say specifically why, but the idea of disabling a trigger on a regular basis makes me uneasy.
Anyway, just my opinion.
dinsdale.
|||
>>I can't say specifically why, but the idea of disabling a trigger on a regular basis makes me uneasy. <<
No doubt!|||
Ah I have not thought about making a check in the trigger.
Why is it bad to disable and enable trigger every time? does it affect performance? data integrity?
This is the first time I use trigger (other than when I'm still a uni student.), so I'm not very experience with the side-effect.
Anyway, that trigger part is now gone, I remove the trigger completely!!, replaced by stored procedure.
My application is adapting to SQL Server quite well.
Regards,
Max
2012年2月19日星期日
Disable Trigger rules
I had created several Trigger rules in current database and it will be
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lotThe only way to disable a trigger is to drop it (or comment out the
T-SQL code in it so it does nothing). If I were you I'd script them out
to a file (with SQLEM), drop them (thereby "disabling" them) and then
schedule the SQL script in the file containing all the trigger code to
run via SQLAgent on Nov 1st (thereby "re-enabling" them).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Agnes wrote:
>I had created several Trigger rules in current database and it will be
>active on 1st Nov,2005,
>Can I disable them now ? and then make them active on that target date '
>Thanks a lot
>
>|||Thanks Mike, I try your method , script it out. (however, it seems I must sc
ript the table structure too.).Any Simple method, i can script trigger rule
only , and the drop them all '
Please be kind to provide some sample .. Thanks in advance. (Today is my 4th
day working about Trigger rule)
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA.279
6@.tk2msftngp13.phx.gbl...
The only way to disable a trigger is to drop it (or comment out the T-SQL co
de in it so it does nothing). If I were you I'd script them out to a file (
with SQLEM), drop them (thereby "disabling" them) and then schedule the SQL
script in the file containing all the trigger code to run via SQLAgent on No
v 1st (thereby "re-enabling" them).
mike hodgson
blog: http://sqlnerd.blogspot.com
Agnes wrote:
I had created several Trigger rules in current database and it will be
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lot|||Hey you have soemthing called
ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
If you want to do it for all tables, use
sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
Regards
R.D
--Knowledge gets doubled when shared
"Agnes" wrote:
> Thanks Mike, I try your method , script it out. (however, it seems I must
script the table structure too.).Any Simple method, i can script trigger rul
e only , and the drop them all '
> Please be kind to provide some sample .. Thanks in advance. (Today is my 4
th day working about Trigger rule)
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA
.2796@.tk2msftngp13.phx.gbl...
> The only way to disable a trigger is to drop it (or comment out the T-SQL code i
n it so it does nothing). If I were you I'd script them out to a file (with SQLEM),
drop them (thereby "disabling" them) and then schedule the SQL script in the file c
ont
aining all the trigger code to run via SQLAgent on Nov 1st (thereby "re-enabling" them).[co
lor=darkred]
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Agnes wrote:
> I had created several Trigger rules in current database and it will be
> active on 1st Nov,2005,
> Can I disable them now ? and then make them active on that target date '
> Thanks a lot
>
>[/color]|||> The only way to disable a trigger is to drop it (or comment out the
> T-SQL code in it so it does nothing).
This is simply not true. Disabling or enabling a trigger is done through the
ALTER TABLE statement.
- to disable a trigger:
ALTER TABLE <table>
DISABLE TRIGGER <trigger>
- to enable a trigger:
ALTER TABLE <table>
ENABLE TRIGGER <trigger>
More here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
ML|||Well you learn something new every day (I don't claim to know
everything). Thanks for the tip - that one's filed away in permanent
cache now. You know, over 10 years working with MSSQL and I'd never
noticed that clause in the ALTER TABLE statement.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
R.D wrote:
>Hey you have soemthing called
>ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
>If you want to do it for all tables, use
>sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
>
>|||I myself was reluctant to use these two methods at first since QA does not
recognize ENABLE and DISABLE as reserved keywords. :)
ML|||Maybe my brain's just not wired correctly, but wouldn't it make more
sense to have the ENABLED/DISABLED clause as part of the ALTER TRIGGER
statement rather than the ALTER TABLE statement? Something like "ALTER
TRIGGER MyTrig ON me.MyTable DISABLE"
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:
>I myself was reluctant to use these two methods at first since QA does not
>recognize ENABLE and DISABLE as reserved keywords. :)
>
>ML
>|||Quite the opposite! Wouldn't it make much more sense if triggers were added
to the objects:
alter table / view <name>
add trigger <trigger_name>
:)
After all - each trigger can only belong to a single table/view...
ML|||True, a trigger can only belong to a single table/view. But ALTER TABLE
is what you use to change the behaviour of an existing trigger (trigger
code, encryption, etc.), so it makes more sense to me that to change an
"attribute" of a trigger, which I would consider its "enabled/disabled"
status to be, you'd use ALTER TRIGGER.
Moot point anyway - purely a hypothetical, but I think the way they've
gone with T-SQL in SQL 2005 is better (with DISABLE TRIGGER being a DDL
statement all of its own).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:
>Quite the opposite! Wouldn't it make much more sense if triggers were added
>to the objects:
>alter table / view <name>
> add trigger <trigger_name>
>:)
>After all - each trigger can only belong to a single table/view...
>
>ML
>
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lotThe only way to disable a trigger is to drop it (or comment out the
T-SQL code in it so it does nothing). If I were you I'd script them out
to a file (with SQLEM), drop them (thereby "disabling" them) and then
schedule the SQL script in the file containing all the trigger code to
run via SQLAgent on Nov 1st (thereby "re-enabling" them).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Agnes wrote:
>I had created several Trigger rules in current database and it will be
>active on 1st Nov,2005,
>Can I disable them now ? and then make them active on that target date '
>Thanks a lot
>
>|||Thanks Mike, I try your method , script it out. (however, it seems I must sc
ript the table structure too.).Any Simple method, i can script trigger rule
only , and the drop them all '
Please be kind to provide some sample .. Thanks in advance. (Today is my 4th
day working about Trigger rule)
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA.279
6@.tk2msftngp13.phx.gbl...
The only way to disable a trigger is to drop it (or comment out the T-SQL co
de in it so it does nothing). If I were you I'd script them out to a file (
with SQLEM), drop them (thereby "disabling" them) and then schedule the SQL
script in the file containing all the trigger code to run via SQLAgent on No
v 1st (thereby "re-enabling" them).
mike hodgson
blog: http://sqlnerd.blogspot.com
Agnes wrote:
I had created several Trigger rules in current database and it will be
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lot|||Hey you have soemthing called
ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
If you want to do it for all tables, use
sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
Regards
R.D
--Knowledge gets doubled when shared
"Agnes" wrote:
> Thanks Mike, I try your method , script it out. (however, it seems I must
script the table structure too.).Any Simple method, i can script trigger rul
e only , and the drop them all '
> Please be kind to provide some sample .. Thanks in advance. (Today is my 4
th day working about Trigger rule)
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA
.2796@.tk2msftngp13.phx.gbl...
> The only way to disable a trigger is to drop it (or comment out the T-SQL code i
n it so it does nothing). If I were you I'd script them out to a file (with SQLEM),
drop them (thereby "disabling" them) and then schedule the SQL script in the file c
ont
aining all the trigger code to run via SQLAgent on Nov 1st (thereby "re-enabling" them).[co
lor=darkred]
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Agnes wrote:
> I had created several Trigger rules in current database and it will be
> active on 1st Nov,2005,
> Can I disable them now ? and then make them active on that target date '
> Thanks a lot
>
>[/color]|||> The only way to disable a trigger is to drop it (or comment out the
> T-SQL code in it so it does nothing).
This is simply not true. Disabling or enabling a trigger is done through the
ALTER TABLE statement.
- to disable a trigger:
ALTER TABLE <table>
DISABLE TRIGGER <trigger>
- to enable a trigger:
ALTER TABLE <table>
ENABLE TRIGGER <trigger>
More here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
ML|||Well you learn something new every day (I don't claim to know
everything). Thanks for the tip - that one's filed away in permanent
cache now. You know, over 10 years working with MSSQL and I'd never
noticed that clause in the ALTER TABLE statement.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
R.D wrote:
>Hey you have soemthing called
>ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
>If you want to do it for all tables, use
>sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
>
>|||I myself was reluctant to use these two methods at first since QA does not
recognize ENABLE and DISABLE as reserved keywords. :)
ML|||Maybe my brain's just not wired correctly, but wouldn't it make more
sense to have the ENABLED/DISABLED clause as part of the ALTER TRIGGER
statement rather than the ALTER TABLE statement? Something like "ALTER
TRIGGER MyTrig ON me.MyTable DISABLE"
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:
>I myself was reluctant to use these two methods at first since QA does not
>recognize ENABLE and DISABLE as reserved keywords. :)
>
>ML
>|||Quite the opposite! Wouldn't it make much more sense if triggers were added
to the objects:
alter table / view <name>
add trigger <trigger_name>
:)
After all - each trigger can only belong to a single table/view...
ML|||True, a trigger can only belong to a single table/view. But ALTER TABLE
is what you use to change the behaviour of an existing trigger (trigger
code, encryption, etc.), so it makes more sense to me that to change an
"attribute" of a trigger, which I would consider its "enabled/disabled"
status to be, you'd use ALTER TRIGGER.
Moot point anyway - purely a hypothetical, but I think the way they've
gone with T-SQL in SQL 2005 is better (with DISABLE TRIGGER being a DDL
statement all of its own).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:
>Quite the opposite! Wouldn't it make much more sense if triggers were added
>to the objects:
>alter table / view <name>
> add trigger <trigger_name>
>:)
>After all - each trigger can only belong to a single table/view...
>
>ML
>
Disable Trigger Question
Here is a trigger in a table that I am working on. Call it tbl1. AT the
bottom of the trigger is an Alter Table statement which looks like it
disables the trigger. My question is for the purpose of this Alter Table
statement at the end of the trigger. I am not familiar with the logic. Doe
s
this run the trigger and then disable it? Or does this statement disable th
e
trigger altogether? If the trigger runs and then gets disabled, what would
be the purpose of doing that?
---
CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
For INSERT, UPDATE
AS
update tbl1
set CoID = c.CoID
from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
where i.CoID <> c.CoID
alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
----
Thanks,
RichWell, I just tried it out on a test trigger and I was able to disable the
trigger this way.
"Rich" wrote:
> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||It looks as if somebody was trying to "manually" avoid what is known as
trigger recursion: a trigger repeatedly calling itself. The trigger modifies
the table that it is defined on, so you would think that it would keep
calling itself in an infinite loop.
SQL server has options for trigger recursion (triggers making changes that
cause themselves to fire again), and nesting (triggers making changes that
fire other triggers). In SQL 2000, trigger recursion is disabled by default.
Even with it enabled, a trigger would call itself a maximum of 32 times.
Recursive triggers are enabled or disabled with ALTER DATABASE .
In SQL 2000, that trigger wouldn't have worked anyway, because DDL
statements were not permitted in triggers. In SQL 2005, they are.
"Rich" wrote:
> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||> In SQL 2000, that trigger wouldn't have worked anyway, because DDL
> statements were not permitted in triggers. In SQL 2005, they are.
Incorrect - this works on sql server 2000.|||Scott Morris" wrote:
> Incorrect - this works on sql server 2000.
>
I stand corrected; however, what's the point of a one-time trigger? If
trigger recursion was even enabled in the first place, wouldn't it be much
better to accomplish the desired action by checking @.@.NESTLEVEL?|||> I stand corrected; however, what's the point of a one-time trigger? If
> trigger recursion was even enabled in the first place, wouldn't it be much
> better to accomplish the desired action by checking @.@.NESTLEVEL?
Indeed - what is the point of such a trigger! That would be a question for
the original programmer. I would fire someone in my employ that did such a
thing - especially since there is no comment in the code to indicate the
reason.|||Create the table and the trigger, look at the trigger code, then
disable the trigger and look at the trigger code again
surprise!!!! last line in the trigger code is "alter table
[dbo].[TestTrigger] disable trigger [trTest]"
So this is not a one time thing, this happens when you disable a
trigger, run code provided to test it out
CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )
INSERT INTO TestTrigger
SELECT 'SQL',500.23
GO
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @.@.ROWCOUNT =0
RETURN
IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO
alter table TestTrigger
disable trigger trTest
Look at the trigger code again
BTW
sp_helptext trTest won't show this, you have to do it from manage
triggers after you right click on the table
http://sqlservercode.blogspot.com/|||On Thu, 2 Feb 2006 11:04:58 -0800, Rich wrote:
>Here is a trigger in a table that I am working on. Call it tbl1. AT the
>bottom of the trigger is an Alter Table statement which looks like it
>disables the trigger. My question is for the purpose of this Alter Table
>statement at the end of the trigger.
Hi Rich,
None - having a trigger that runs only once, then disables itself has no
purpose at all.
The script posted by "SQL" reveals the bug that caused this alter table
statement to be in the trigger.
If you use Enterprise Manager to manage a trigger (right-click a table;
select All Tasks / Manage Triggers), you'll get a window in which is
some generated code that will recreate a trigger in it's current state.
For a normal trigger, that would be:
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
And if the trigger is currently disabled, that should be extended to
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
GO
ALTER TABLE table DISABLE TRIGGER name
Unfortunately, there seems to be a bug in Enterpris Manager - it omits
the batch seperator ("GO") in the generated code. That makes a huge
difference. With the GO, the trigger gets recreated in it's original
form, then (in a seperate batch) disabled. Without the GO, the trigger
gets recreated with an extra ALTER TABLE statement tacked on at the end
of the code, and is kept in an enabled state. Of course, after being
fired once, the trigger will be disabled again, due to the execution of
the extra ALTER TABLE statement.
It really gets funny if you repeat the exercise a few times. Starting
from the code posted by "SQL", I have now a trigger with this code:
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
(snip)
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
(I omitted the blank lines to keep the post readable).
To fix this, take the following steps:
1. Find out if the trigger needs to be enabled or disabled in your
applications. If disabled, you might want to check if it will ever be
needed again.
2. Copy the current code of the trigger into Query Analyzer, remove the
ALTER TABLE statement and recreate the trigger. Then, if necessary,
disable it. (Doon't forget to add a GO statement if you intend to do
this all in one script <g> ).
3. Make a mental note to never ever use Enterprise Manager again for
creating or changing triggers. (Same goes for lots of other tasks too;
EM is really only a DBA tool; development work should be done in Query
Analyzer).
Hugo Kornelis, SQL Server MVP
bottom of the trigger is an Alter Table statement which looks like it
disables the trigger. My question is for the purpose of this Alter Table
statement at the end of the trigger. I am not familiar with the logic. Doe
s
this run the trigger and then disable it? Or does this statement disable th
e
trigger altogether? If the trigger runs and then gets disabled, what would
be the purpose of doing that?
---
CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
For INSERT, UPDATE
AS
update tbl1
set CoID = c.CoID
from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
where i.CoID <> c.CoID
alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
----
Thanks,
RichWell, I just tried it out on a test trigger and I was able to disable the
trigger this way.
"Rich" wrote:
> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||It looks as if somebody was trying to "manually" avoid what is known as
trigger recursion: a trigger repeatedly calling itself. The trigger modifies
the table that it is defined on, so you would think that it would keep
calling itself in an infinite loop.
SQL server has options for trigger recursion (triggers making changes that
cause themselves to fire again), and nesting (triggers making changes that
fire other triggers). In SQL 2000, trigger recursion is disabled by default.
Even with it enabled, a trigger would call itself a maximum of 32 times.
Recursive triggers are enabled or disabled with ALTER DATABASE .
In SQL 2000, that trigger wouldn't have worked anyway, because DDL
statements were not permitted in triggers. In SQL 2005, they are.
"Rich" wrote:
> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||> In SQL 2000, that trigger wouldn't have worked anyway, because DDL
> statements were not permitted in triggers. In SQL 2005, they are.
Incorrect - this works on sql server 2000.|||Scott Morris" wrote:
> Incorrect - this works on sql server 2000.
>
I stand corrected; however, what's the point of a one-time trigger? If
trigger recursion was even enabled in the first place, wouldn't it be much
better to accomplish the desired action by checking @.@.NESTLEVEL?|||> I stand corrected; however, what's the point of a one-time trigger? If
> trigger recursion was even enabled in the first place, wouldn't it be much
> better to accomplish the desired action by checking @.@.NESTLEVEL?
Indeed - what is the point of such a trigger! That would be a question for
the original programmer. I would fire someone in my employ that did such a
thing - especially since there is no comment in the code to indicate the
reason.|||Create the table and the trigger, look at the trigger code, then
disable the trigger and look at the trigger code again
surprise!!!! last line in the trigger code is "alter table
[dbo].[TestTrigger] disable trigger [trTest]"
So this is not a one time thing, this happens when you disable a
trigger, run code provided to test it out
CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )
INSERT INTO TestTrigger
SELECT 'SQL',500.23
GO
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @.@.ROWCOUNT =0
RETURN
IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO
alter table TestTrigger
disable trigger trTest
Look at the trigger code again
BTW
sp_helptext trTest won't show this, you have to do it from manage
triggers after you right click on the table
http://sqlservercode.blogspot.com/|||On Thu, 2 Feb 2006 11:04:58 -0800, Rich wrote:
>Here is a trigger in a table that I am working on. Call it tbl1. AT the
>bottom of the trigger is an Alter Table statement which looks like it
>disables the trigger. My question is for the purpose of this Alter Table
>statement at the end of the trigger.
Hi Rich,
None - having a trigger that runs only once, then disables itself has no
purpose at all.
The script posted by "SQL" reveals the bug that caused this alter table
statement to be in the trigger.
If you use Enterprise Manager to manage a trigger (right-click a table;
select All Tasks / Manage Triggers), you'll get a window in which is
some generated code that will recreate a trigger in it's current state.
For a normal trigger, that would be:
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
And if the trigger is currently disabled, that should be extended to
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
GO
ALTER TABLE table DISABLE TRIGGER name
Unfortunately, there seems to be a bug in Enterpris Manager - it omits
the batch seperator ("GO") in the generated code. That makes a huge
difference. With the GO, the trigger gets recreated in it's original
form, then (in a seperate batch) disabled. Without the GO, the trigger
gets recreated with an extra ALTER TABLE statement tacked on at the end
of the code, and is kept in an enabled state. Of course, after being
fired once, the trigger will be disabled again, due to the execution of
the extra ALTER TABLE statement.
It really gets funny if you repeat the exercise a few times. Starting
from the code posted by "SQL", I have now a trigger with this code:
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
(snip)
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
(I omitted the blank lines to keep the post readable).
To fix this, take the following steps:
1. Find out if the trigger needs to be enabled or disabled in your
applications. If disabled, you might want to check if it will ever be
needed again.
2. Copy the current code of the trigger into Query Analyzer, remove the
ALTER TABLE statement and recreate the trigger. Then, if necessary,
disable it. (Doon't forget to add a GO statement if you intend to do
this all in one script <g> ).
3. Make a mental note to never ever use Enterprise Manager again for
creating or changing triggers. (Same goes for lots of other tasks too;
EM is really only a DBA tool; development work should be done in Query
Analyzer).
Hugo Kornelis, SQL Server MVP
DISABLE TRIGGER
How can i disable a trigger'
Any suggestion appreciated.
Message posted via http://www.webservertalk.comHi
-- to diable all
EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
GO
-- to enable all
EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
Note : sp_MSForEachTable is undocumented ans unsupported
"akej via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:06352efd0e4e43029b575e79a35d51ca@.SQ
webservertalk.com...
> How can i disable a trigger'
> Any suggestion appreciated.
> --
> Message posted via http://www.webservertalk.com|||So maybe better to drop(disable) and afete create(enable)
Message posted via http://www.webservertalk.com|||No, no need to drop and recreate. Simply use
ALTER TABLE table_name DISABLE TRIGGER trigger_name
Replace names as appropriate. Also, to disable all triggers, as Uri already
suggested, replace trigger_name with ALL keyword.
To enable the trigger again, replace DISABLE with ENABLE.
Regards,
Raj|||What about to disable/enable constraint, foreign key constraint '
Message posted via http://www.webservertalk.com
Any suggestion appreciated.
Message posted via http://www.webservertalk.comHi
-- to diable all
EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
GO
-- to enable all
EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
Note : sp_MSForEachTable is undocumented ans unsupported
"akej via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:06352efd0e4e43029b575e79a35d51ca@.SQ
webservertalk.com...
> How can i disable a trigger'
> Any suggestion appreciated.
> --
> Message posted via http://www.webservertalk.com|||So maybe better to drop(disable) and afete create(enable)
Message posted via http://www.webservertalk.com|||No, no need to drop and recreate. Simply use
ALTER TABLE table_name DISABLE TRIGGER trigger_name
Replace names as appropriate. Also, to disable all triggers, as Uri already
suggested, replace trigger_name with ALL keyword.
To enable the trigger again, replace DISABLE with ENABLE.
Regards,
Raj|||What about to disable/enable constraint, foreign key constraint '
Message posted via http://www.webservertalk.com
Disable trigger
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 doesnt 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.I think you want ALTER TABLE myTable DISABLE TRIGGER ALL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_9036.asp) or something very much like it.
-PatP|||fix the procedure?|||fix the procedure?Oh now there's a silly answer!
Fix the problem instead of trying to find a work-around ?!?! Sheeshka, what will they think of next?
-PatP|||What was I thinking...|||Oooh ... oooh - I know the answer to this one :rolleyes: . Create a separate userid and have the trigger check to see if that userid ran the proc that touched the table that fired the trigger. If that user is the active user bypass the trigger, otherwise fire the trigger!
At least, that's what some ** person ** did to a critical set of procs and triggers before I started here, and it would take an act of (insert the appropriate authority here) to do it properly :o .
What some folks won't think of!!|||I actually did exactly that in MS-SQL 6.5 to work around a problem in propriatary code (we didn't have the source to fix the underlying problem).
-PatP
can I disable a trigger in Sqlserver 2000? When i run a store procedure who works with one table i want that the trigger doesnt 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.I think you want ALTER TABLE myTable DISABLE TRIGGER ALL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_9036.asp) or something very much like it.
-PatP|||fix the procedure?|||fix the procedure?Oh now there's a silly answer!
Fix the problem instead of trying to find a work-around ?!?! Sheeshka, what will they think of next?
-PatP|||What was I thinking...|||Oooh ... oooh - I know the answer to this one :rolleyes: . Create a separate userid and have the trigger check to see if that userid ran the proc that touched the table that fired the trigger. If that user is the active user bypass the trigger, otherwise fire the trigger!
At least, that's what some ** person ** did to a critical set of procs and triggers before I started here, and it would take an act of (insert the appropriate authority here) to do it properly :o .
What some folks won't think of!!|||I actually did exactly that in MS-SQL 6.5 to work around a problem in propriatary code (we didn't have the source to fix the underlying problem).
-PatP
Disable trigger
Sorry, may be it is very simple, but
how can I disable all trigger on a tabled under sql 2000Originally posted by xiangmeihong
Sorry, may be it is very simple, but
how can I disable all trigger on a tabled under sql 2000
sorry
how can I disable all trigger on a table under sql 2000|||REfer books online for ALTER TABLE syntax to disable/enable trigger as specified.|||Originally posted by Satya
REfer books online for ALTER TABLE syntax to disable/enable trigger as specified.
THANK YOU!
how can I disable all trigger on a tabled under sql 2000Originally posted by xiangmeihong
Sorry, may be it is very simple, but
how can I disable all trigger on a tabled under sql 2000
sorry
how can I disable all trigger on a table under sql 2000|||REfer books online for ALTER TABLE syntax to disable/enable trigger as specified.|||Originally posted by Satya
REfer books online for ALTER TABLE syntax to disable/enable trigger as specified.
THANK YOU!
disable trigger
Is it possible to disable a Trigger for a particular type of transaction?
There are 2 transactions running A,B..I want the trigger to be disabled for Transaction 'A' but enable it for 'B',even if they are running at the same time?Not directly
--BY SP
if COLUMNS_UPDATED()>0 and 0<trigger_nestlevel(object_id('SP Name')) return
if @.@.rowcount=0 return
if exists(select 'x' from inserted) and 0<trigger_nestlevel(object_id('SP Name')) return
if exists(select 'x' from deleted ) and 0<trigger_nestlevel(object_id('SP Name')) return
--BY TABLE
IF COLUMNS_UPDATED()>0 begin
if exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='U') return
end else begin
if @.@.rowcount=0 return
if exists(select 'x' from inserted) and exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='I') return
if exists(select 'x' from deleted ) and exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='D') return
end
Good luck !
There are 2 transactions running A,B..I want the trigger to be disabled for Transaction 'A' but enable it for 'B',even if they are running at the same time?Not directly
--BY SP
if COLUMNS_UPDATED()>0 and 0<trigger_nestlevel(object_id('SP Name')) return
if @.@.rowcount=0 return
if exists(select 'x' from inserted) and 0<trigger_nestlevel(object_id('SP Name')) return
if exists(select 'x' from deleted ) and 0<trigger_nestlevel(object_id('SP Name')) return
--BY TABLE
IF COLUMNS_UPDATED()>0 begin
if exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='U') return
end else begin
if @.@.rowcount=0 return
if exists(select 'x' from inserted) and exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='I') return
if exists(select 'x' from deleted ) and exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='D') return
end
Good luck !
2012年2月14日星期二
disable and enable trigger on schedule
How do you disable and enable the trigger on a table automatically at some
time daily?Hi,
Identify the triggers which needs to be disabled. Then use the below Alter
command to disable
1. Create a SQL Agent job to disable the trigger and schedule a time
ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
2. Create another SQL Agent job to enable the trigger and schedule a time
ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
Make sure data integrity is not lost when you disable the trigger.
Thanks
Hari
SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
> How do you disable and enable the trigger on a table automatically at some
> time daily?
>|||Hi,
Thanks Hari for greate information. I just like to provide following link
for detailed information:
http://msdn2.microsoft.com/en-us/library/ms182706.aspx
http://msdn2.microsoft.com/en-us/library/ms189748.aspx
Hope this helps.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "Hari Prasad" <hari_prasad_k@.hotmail.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Tue, 5 Sep 2006 20:56:58 -0500
>>Lines: 26
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>>Message-ID: <#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: cpe-65-27-44-57.kc.res.rr.com 65.27.44.57
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444272
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi,
>>Identify the triggers which needs to be disabled. Then use the below
Alter
>>command to disable
>>1. Create a SQL Agent job to disable the trigger and schedule a time
>>ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>>2. Create another SQL Agent job to enable the trigger and schedule a time
>>ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>>Make sure data integrity is not lost when you disable the trigger.
>>Thanks
>>Hari
>>SQL Server MVP
>>"00KobeBrian" <a@.b.com> wrote in message
>>news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
some
>> time daily?
>>
>>|||How can I know if the trigger is enabled or disabled? Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Identify the triggers which needs to be disabled. Then use the below Alter
> command to disable
> 1. Create a SQL Agent job to disable the trigger and schedule a time
> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
> 2. Create another SQL Agent job to enable the trigger and schedule a time
> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
> Make sure data integrity is not lost when you disable the trigger.
> Thanks
> Hari
> SQL Server MVP
> "00KobeBrian" <a@.b.com> wrote in message
> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>|||Hi 00KobeBrian
Use the function OBJECTPROPERTY
SELECT OBJECTPROPERTY(object_id('name of trigger')',
'ExecIsTriggerDisabled')
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
> How can I know if the trigger is enabled or disabled? Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>|||Hi,
If you are using SQL 2005, you can refer to following article:
<http://msdn2.microsoft.com/en-us/library/ms188746.aspx>
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
<#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Wed, 6 Sep 2006 12:52:34 +0800
>>Lines: 33
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 202.40.134.130
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444283
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I know if the trigger is enabled or disabled? Thanks.
>>
>>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>>news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
Alter
>> command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>|||Can you please give me an example? I am using SQL 2000. Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
> Hi 00KobeBrian
> Use the function OBJECTPROPERTY
> SELECT OBJECTPROPERTY(object_id('name of trigger')',
> 'ExecIsTriggerDisabled')
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>|||I gave you an example of checking for whether a trigger is disabled. If the
SELECT OBJECTPROPERTY returns 1, the trigger is disabled, if it returns 0,
the trigger is enabled, if it returns NULL, you typed something incorrectly.
If you need more details than that, you'll have to be more specific about
what you don't understand.
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:O5vt5%23X0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Can you please give me an example? I am using SQL 2000. Thanks.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
>> Hi 00KobeBrian
>> Use the function OBJECTPROPERTY
>> SELECT OBJECTPROPERTY(object_id('name of trigger')',
>> 'ExecIsTriggerDisabled')
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>
>
time daily?Hi,
Identify the triggers which needs to be disabled. Then use the below Alter
command to disable
1. Create a SQL Agent job to disable the trigger and schedule a time
ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
2. Create another SQL Agent job to enable the trigger and schedule a time
ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
Make sure data integrity is not lost when you disable the trigger.
Thanks
Hari
SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
> How do you disable and enable the trigger on a table automatically at some
> time daily?
>|||Hi,
Thanks Hari for greate information. I just like to provide following link
for detailed information:
http://msdn2.microsoft.com/en-us/library/ms182706.aspx
http://msdn2.microsoft.com/en-us/library/ms189748.aspx
Hope this helps.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "Hari Prasad" <hari_prasad_k@.hotmail.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Tue, 5 Sep 2006 20:56:58 -0500
>>Lines: 26
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>>Message-ID: <#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: cpe-65-27-44-57.kc.res.rr.com 65.27.44.57
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444272
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi,
>>Identify the triggers which needs to be disabled. Then use the below
Alter
>>command to disable
>>1. Create a SQL Agent job to disable the trigger and schedule a time
>>ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>>2. Create another SQL Agent job to enable the trigger and schedule a time
>>ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>>Make sure data integrity is not lost when you disable the trigger.
>>Thanks
>>Hari
>>SQL Server MVP
>>"00KobeBrian" <a@.b.com> wrote in message
>>news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
some
>> time daily?
>>
>>|||How can I know if the trigger is enabled or disabled? Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Identify the triggers which needs to be disabled. Then use the below Alter
> command to disable
> 1. Create a SQL Agent job to disable the trigger and schedule a time
> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
> 2. Create another SQL Agent job to enable the trigger and schedule a time
> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
> Make sure data integrity is not lost when you disable the trigger.
> Thanks
> Hari
> SQL Server MVP
> "00KobeBrian" <a@.b.com> wrote in message
> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>|||Hi 00KobeBrian
Use the function OBJECTPROPERTY
SELECT OBJECTPROPERTY(object_id('name of trigger')',
'ExecIsTriggerDisabled')
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
> How can I know if the trigger is enabled or disabled? Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>|||Hi,
If you are using SQL 2005, you can refer to following article:
<http://msdn2.microsoft.com/en-us/library/ms188746.aspx>
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
<#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Wed, 6 Sep 2006 12:52:34 +0800
>>Lines: 33
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 202.40.134.130
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444283
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I know if the trigger is enabled or disabled? Thanks.
>>
>>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>>news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
Alter
>> command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>|||Can you please give me an example? I am using SQL 2000. Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
> Hi 00KobeBrian
> Use the function OBJECTPROPERTY
> SELECT OBJECTPROPERTY(object_id('name of trigger')',
> 'ExecIsTriggerDisabled')
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>|||I gave you an example of checking for whether a trigger is disabled. If the
SELECT OBJECTPROPERTY returns 1, the trigger is disabled, if it returns 0,
the trigger is enabled, if it returns NULL, you typed something incorrectly.
If you need more details than that, you'll have to be more specific about
what you don't understand.
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:O5vt5%23X0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Can you please give me an example? I am using SQL 2000. Thanks.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
>> Hi 00KobeBrian
>> Use the function OBJECTPROPERTY
>> SELECT OBJECTPROPERTY(object_id('name of trigger')',
>> 'ExecIsTriggerDisabled')
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>
>
disable and enable trigger on schedule
How do you disable and enable the trigger on a table automatically at some
time daily?Hi,
Identify the triggers which needs to be disabled. Then use the below Alter
command to disable
1. Create a SQL Agent job to disable the trigger and schedule a time
ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
2. Create another SQL Agent job to enable the trigger and schedule a time
ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
Make sure data integrity is not lost when you disable the trigger.
Thanks
Hari
SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
> How do you disable and enable the trigger on a table automatically at some
> time daily?
>|||Hi,
Thanks Hari for greate information. I just like to provide following link
for detailed information:
http://msdn2.microsoft.com/en-us/library/ms182706.aspx
http://msdn2.microsoft.com/en-us/library/ms189748.aspx
Hope this helps.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
Alter[vbcol=seagreen]
some[vbcol=seagreen]|||How can I know if the trigger is enabled or disabled? Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Identify the triggers which needs to be disabled. Then use the below Alter
> command to disable
> 1. Create a SQL Agent job to disable the trigger and schedule a time
> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
> 2. Create another SQL Agent job to enable the trigger and schedule a time
> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
> Make sure data integrity is not lost when you disable the trigger.
> Thanks
> Hari
> SQL Server MVP
> "00KobeBrian" <a@.b.com> wrote in message
> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>|||Hi 00KobeBrian
Use the function OBJECTPROPERTY
SELECT OBJECTPROPERTY(object_id('name of trigger')',
'ExecIsTriggerDisabled')
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
> How can I know if the trigger is enabled or disabled? Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>|||Hi,
If you are using SQL 2005, you can refer to following article:
<http://msdn2.microsoft.com/en-us/library/ms188746.aspx>
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>[vbcol=seagreen]
Alter[vbcol=seagreen]
time[vbcol=seagreen]|||Can you please give me an example? I am using SQL 2000. Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
> Hi 00KobeBrian
> Use the function OBJECTPROPERTY
> SELECT OBJECTPROPERTY(object_id('name of trigger')',
> 'ExecIsTriggerDisabled')
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>|||I gave you an example of checking for whether a trigger is disabled. If the
SELECT OBJECTPROPERTY returns 1, the trigger is disabled, if it returns 0,
the trigger is enabled, if it returns NULL, you typed something incorrectly.
If you need more details than that, you'll have to be more specific about
what you don't understand.
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:O5vt5%23X0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Can you please give me an example? I am using SQL 2000. Thanks.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
>
time daily?Hi,
Identify the triggers which needs to be disabled. Then use the below Alter
command to disable
1. Create a SQL Agent job to disable the trigger and schedule a time
ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
2. Create another SQL Agent job to enable the trigger and schedule a time
ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
Make sure data integrity is not lost when you disable the trigger.
Thanks
Hari
SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
> How do you disable and enable the trigger on a table automatically at some
> time daily?
>|||Hi,
Thanks Hari for greate information. I just like to provide following link
for detailed information:
http://msdn2.microsoft.com/en-us/library/ms182706.aspx
http://msdn2.microsoft.com/en-us/library/ms189748.aspx
Hope this helps.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
Alter[vbcol=seagreen]
some[vbcol=seagreen]|||How can I know if the trigger is enabled or disabled? Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Identify the triggers which needs to be disabled. Then use the below Alter
> command to disable
> 1. Create a SQL Agent job to disable the trigger and schedule a time
> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
> 2. Create another SQL Agent job to enable the trigger and schedule a time
> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
> Make sure data integrity is not lost when you disable the trigger.
> Thanks
> Hari
> SQL Server MVP
> "00KobeBrian" <a@.b.com> wrote in message
> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>|||Hi 00KobeBrian
Use the function OBJECTPROPERTY
SELECT OBJECTPROPERTY(object_id('name of trigger')',
'ExecIsTriggerDisabled')
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
> How can I know if the trigger is enabled or disabled? Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>|||Hi,
If you are using SQL 2005, you can refer to following article:
<http://msdn2.microsoft.com/en-us/library/ms188746.aspx>
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>[vbcol=seagreen]
Alter[vbcol=seagreen]
time[vbcol=seagreen]|||Can you please give me an example? I am using SQL 2000. Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
> Hi 00KobeBrian
> Use the function OBJECTPROPERTY
> SELECT OBJECTPROPERTY(object_id('name of trigger')',
> 'ExecIsTriggerDisabled')
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>|||I gave you an example of checking for whether a trigger is disabled. If the
SELECT OBJECTPROPERTY returns 1, the trigger is disabled, if it returns 0,
the trigger is enabled, if it returns NULL, you typed something incorrectly.
If you need more details than that, you'll have to be more specific about
what you don't understand.
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:O5vt5%23X0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Can you please give me an example? I am using SQL 2000. Thanks.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
>
订阅：
博文 (Atom)