Display date: update

显示标签为“update”的博文。显示所有博文

2012年2月24日星期五

Disable update to a table

I want to be able to disable updates to a specific table from the SQL prompt
i.e the Query Analyzer. This table can only be update from the front end
screens. How do I accomplish this?
VJDon't give the users who have QA permissions to update that table.
Andrew J. Kelly
SQL Server MVP
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:OpI6o0b8DHA.1636@.TK2MSFTNGP12.phx.gbl...
> I want to be able to disable updates to a specific table from the SQL
prompt
> i.e the Query Analyzer. This table can only be update from the front end
> screens. How do I accomplish this?
> VJ
>|||>> I want to be able to disable updates to a specific table
> Don't give the users who have QA permissions to update that table.
If updates = data changes, you may also try APP_NAME() +
UPDATE/INSERT/DELETE triggers, allowing you to trap these operations and
react to them.
If updates = table schema changes, in Yukon you may also try APP_NAME() +
Data Definition Language (DDL) triggers, allowing you to trap DDL operations
and react to them, optionally rolling back the activity.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
You can try application roles . which will allow user access specific to
your application . You can restrict query analyzer .
Regards
Ajay
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:OHXqzYc8DHA.488@.TK2MSFTNGP12.phx.gbl...
> If updates = data changes, you may also try APP_NAME() +
> UPDATE/INSERT/DELETE triggers, allowing you to trap these operations and
> react to them.
> If updates = table schema changes, in Yukon you may also try APP_NAME() +
> Data Definition Language (DDL) triggers, allowing you to trap DDL
operations
> and react to them, optionally rolling back the activity.
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Yet another method is to create an instead of trigger for update on the
table and do nothing in that trigger.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ORhLk1f8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> You can try application roles . which will allow user access specific to
> your application . You can restrict query analyzer .
> Regards
> Ajay
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:OHXqzYc8DHA.488@.TK2MSFTNGP12.phx.gbl...
+
> operations
> rights.
>

Disable update to a table

I want to be able to disable updates to a specific table from the SQL prompt
i.e the Query Analyzer. This table can only be update from the front end
screens. How do I accomplish this?
VJDon't give the users who have QA permissions to update that table.
--
Andrew J. Kelly
SQL Server MVP
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:OpI6o0b8DHA.1636@.TK2MSFTNGP12.phx.gbl...
> I want to be able to disable updates to a specific table from the SQL
prompt
> i.e the Query Analyzer. This table can only be update from the front end
> screens. How do I accomplish this?
> VJ
>|||>> I want to be able to disable updates to a specific table
> Don't give the users who have QA permissions to update that table.
If updates = data changes, you may also try APP_NAME() +
UPDATE/INSERT/DELETE triggers, allowing you to trap these operations and
react to them.
If updates = table schema changes, in Yukon you may also try APP_NAME() +
Data Definition Language (DDL) triggers, allowing you to trap DDL operations
and react to them, optionally rolling back the activity.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
You can try application roles . which will allow user access specific to
your application . You can restrict query analyzer .
Regards
Ajay
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:OHXqzYc8DHA.488@.TK2MSFTNGP12.phx.gbl...
> >> I want to be able to disable updates to a specific table
> > Don't give the users who have QA permissions to update that table.
> If updates = data changes, you may also try APP_NAME() +
> UPDATE/INSERT/DELETE triggers, allowing you to trap these operations and
> react to them.
> If updates = table schema changes, in Yukon you may also try APP_NAME() +
> Data Definition Language (DDL) triggers, allowing you to trap DDL
operations
> and react to them, optionally rolling back the activity.
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Yet another method is to create an instead of trigger for update on the
table and do nothing in that trigger.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ORhLk1f8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> You can try application roles . which will allow user access specific to
> your application . You can restrict query analyzer .
> Regards
> Ajay
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:OHXqzYc8DHA.488@.TK2MSFTNGP12.phx.gbl...
> > >> I want to be able to disable updates to a specific table
> > > Don't give the users who have QA permissions to update that table.
> >
> > If updates = data changes, you may also try APP_NAME() +
> > UPDATE/INSERT/DELETE triggers, allowing you to trap these operations and
> > react to them.
> >
> > If updates = table schema changes, in Yukon you may also try APP_NAME()
+
> > Data Definition Language (DDL) triggers, allowing you to trap DDL
> operations
> > and react to them, optionally rolling back the activity.
> >
> > sincerely,
> > --
> > Sebastian K. Zaklada
> > Skilled Software
> > http://www.skilledsoftware.com
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
>

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

2012年2月17日星期五

disable job

I'm using SQL server 2000
when i disable or delete a job, it returns
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."Win
Examine MSDB..sysjobs system table. Probably there is ni match a server name
in originating_server column
"Win" <aaa@.aaa.com> wrote in message
news:%23Br9haUUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> I'm using SQL server 2000
> when i disable or delete a job, it returns
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
>
>
>

2012年2月14日星期二

disable and enable triggers

hi,
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanksmecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>

订阅：博文 (Atom)

2012年2月24日星期五

Disable update to a table

Disable update to a table

Disable triggers for a single update

2012年2月17日星期五

disable job

2012年2月14日星期二

disable and enable triggers

disable and enable triggers

disable and enable triggers

Display date

Blog Archive

About Me