Disable updates via triggers - how?

Hi,
We use push transactional replication successfully and are about to bring a
few remote on-sites into the fold. The connection speed will be low - in the
64k/128k range. As I understand it "Not for replication" means that a trigger
on the subcriber will not fire if the update is generated via a replication
process, i.e. all updates are pushed from the publisher. If this is correct
is there any way to reverse the logic, i.e. don't replicate updates on the
publisher that are caused by triggers and assume the subscriber will have the
appropriate triggers in place to do the update.
We are interested in doing this to reduce the amount of network bandwidth
consummed be replication (this will be critical when the remote sites come
on-line).
Can this be done?
Cheers, Peter
The not for replication switch means that replication related activity
will not cause the trigger, constraint, identity property to be enforced
if the triggering activity occurs by a replication process. This could
happen on the publisher or subscriber.
There is a way to bypass the replication process. What you do is add
filters to your tables which look like this
select * from published tables where 1=1.
A filter proc will be created which the log reader agent uses when
figuring out what commands are to be written to the distribution agent.
Change this filter to always return 0 when you want the commands to be
ignored. Change it back to 1 when you want the commands to be processed.
The problem is you can't alter procs via a trigger.
The way I handle things like this is to build the logic into the
replication proc that applies these transactions on the subscriber(s).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Hi Hillary,
Thanks for the response.
Where do I go to add the filter: "select * from published tables where 1=1."?
When and where would I change the filter?
I do have a copy of your fine book - what sections are relevent to this
particular issue.
I presume the following isn't relevent to my circumstance as what I'm trying
to do reduce network traffice. Or I have I misunderstood?

> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
Cheers, Peter
"Hilary Cotter" wrote:

> The not for replication switch means that replication related activity
> will not cause the trigger, constraint, identity property to be enforced
> if the triggering activity occurs by a replication process. This could
> happen on the publisher or subscriber.
> There is a way to bypass the replication process. What you do is add
> filters to your tables which look like this
>
> A filter proc will be created which the log reader agent uses when
> figuring out what commands are to be written to the distribution agent.
> Change this filter to always return 0 when you want the commands to be
> ignored. Change it back to 1 when you want the commands to be processed.
> The problem is you can't alter procs via a trigger.
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
|||You are correct, making these change to the replication procs used to
synchronize your data will not minimize network traffic. Replicating the
execution of stored procedures will, if the majority of your transactions
occurring on the publisher affect more than one row.
Have a look at this post for an example of how to make the log reader agent
bypass transactions.
http://groups-beta.google.com/group/...c?dmode=source
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:87E55BFF-BCB2-4C20-B5F5-9C48B3B8064D@.microsoft.com...[vbcol=seagreen]
> Hi Hillary,
> Thanks for the response.
> Where do I go to add the filter: "select * from published tables where
> 1=1."?
> When and where would I change the filter?
> I do have a copy of your fine book - what sections are relevent to this
> particular issue.
> I presume the following isn't relevent to my circumstance as what I'm
> trying
> to do reduce network traffice. Or I have I misunderstood?
>
> Cheers, Peter
>
> "Hilary Cotter" wrote:

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