Is it possible to control the actions of a trigger based on the user who
updates the record?
In pseudo code, I'm trying to do the following:
on update:
if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
{ newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
Could someone shead some light on if/how this could be done in "real" code?
Any help would be GREATLY appreciated.
Thanks,
_KThe trigger's code should look very similar to your pseudo code (not
tested):
IF SUSER_SNAME() = 'User_A'
BEGIN
UPDATE T1
SET PIC_FIELD = D.PIC_FIELD
FROM T1 JOIN deleted AS D
ON T1.key = D.key
WHERE T1.Artist_Type IN('DJ', 'CL')
END
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
> Is it possible to control the actions of a trigger based on the user who
> updates the record?
> In pseudo code, I'm trying to do the following:
> on update:
> if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
> { newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
>
> Could someone shead some light on if/how this could be done in "real"
> code?
> Any help would be GREATLY appreciated.
> Thanks,
> _K
>|||Thanks VERY much.
Would it still be deleted if this is an update trigger?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
> The trigger's code should look very similar to your pseudo code (not
> tested):
> IF SUSER_SNAME() = 'User_A'
> BEGIN
> UPDATE T1
> SET PIC_FIELD = D.PIC_FIELD
> FROM T1 JOIN deleted AS D
> ON T1.key = D.key
> WHERE T1.Artist_Type IN('DJ', 'CL')
> END
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "KBryan" <kbryan@.noyouwont.com> wrote in message
> news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
>|||Yes; deleted holds the old image of the modified data.
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%23WFXaMXNFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Thanks VERY much.
> Would it still be deleted if this is an update trigger?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>
Showing posts with label actions. Show all posts
Showing posts with label actions. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
Controling Replation agent actions
Hello there
I some questions:
1. Iw'd like to run some store procedures before i'm starging the
replication every day. Dose it enouth to add the task before the replication
task?
2. If the replication faild i would like to make sure that none of the
changes will be made.
3. IF an error occur can i nevegat it to the type of error:
if the error is data error(validation of foreign key) or connection error.
So i can give it order to run 5 minutes afterword?
4. Can i output the errors to outside files in order that other programs
that use sql server (Access) can give a message to the user?
Roy,
adding another step would be fine, or another job which runs sp_start_job
after your code had finished.
Trapping errors is really well catered fro in SQL 2005 but not so easy in
SQL 2000. You'd be better testing your changes in code rather than
attempting your changes eg look for a PK value before trying the FK insert.
If the PK doesn't exist, then logging this to a table and taking appropriate
actions eg making your own error message available to Access.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||1) I would make these procs the first job step of your replication job
2) Double click on the job step, select advanced and have it run a job or
script on failure.
3) This is a little tricky - the error should be in the msrepl_errors table
in the distribution database where the agent is run. You can query it there,
but the error may not be there depending on the error message. What you
would need to do is restart the agent on failure, but this time use the
verbose agent profile. The complete error message will now be in the
msrepl_Errors table. Errors are also logged in text files and dumped in
%WindDir%\system32 and will have an err extension. You can poll for them
using FileSystemWatcher.
4) You might want to poll msrepl_errors as discussed above and write to an
access database. Or you could fire something using the Replication Alert
Agent Failure.
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
> Hello there
> I some questions:
> 1. Iw'd like to run some store procedures before i'm starging the
> replication every day. Dose it enouth to add the task before the
> replication
> task?
> 2. If the replication faild i would like to make sure that none of the
> changes will be made.
> 3. IF an error occur can i nevegat it to the type of error:
> if the error is data error(validation of foreign key) or connection error.
> So i can give it order to run 5 minutes afterword?
> 4. Can i output the errors to outside files in order that other programs
> that use sql server (Access) can give a message to the user?
>
>
|||Whell Hilary
I've made an error in the replication and no MSRepl_Errors table were
created
After i got the error when i run the agent on the enterprise manager i
opened the query anlyser with the distribution database and i counldn't see
the table
When will the table appear?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYG7KczAGHA.272@.TK2MSFTNGP09.phx.gbl...
> 1) I would make these procs the first job step of your replication job
> 2) Double click on the job step, select advanced and have it run a job or
> script on failure.
> 3) This is a little tricky - the error should be in the msrepl_errors
> table in the distribution database where the agent is run. You can query
> it there, but the error may not be there depending on the error message.
> What you would need to do is restart the agent on failure, but this time
> use the verbose agent profile. The complete error message will now be in
> the msrepl_Errors table. Errors are also logged in text files and dumped
> in %WindDir%\system32 and will have an err extension. You can poll for
> them using FileSystemWatcher.
> 4) You might want to poll msrepl_errors as discussed above and write to an
> access database. Or you could fire something using the Replication Alert
> Agent Failure.
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
>
I some questions:
1. Iw'd like to run some store procedures before i'm starging the
replication every day. Dose it enouth to add the task before the replication
task?
2. If the replication faild i would like to make sure that none of the
changes will be made.
3. IF an error occur can i nevegat it to the type of error:
if the error is data error(validation of foreign key) or connection error.
So i can give it order to run 5 minutes afterword?
4. Can i output the errors to outside files in order that other programs
that use sql server (Access) can give a message to the user?
Roy,
adding another step would be fine, or another job which runs sp_start_job
after your code had finished.
Trapping errors is really well catered fro in SQL 2005 but not so easy in
SQL 2000. You'd be better testing your changes in code rather than
attempting your changes eg look for a PK value before trying the FK insert.
If the PK doesn't exist, then logging this to a table and taking appropriate
actions eg making your own error message available to Access.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||1) I would make these procs the first job step of your replication job
2) Double click on the job step, select advanced and have it run a job or
script on failure.
3) This is a little tricky - the error should be in the msrepl_errors table
in the distribution database where the agent is run. You can query it there,
but the error may not be there depending on the error message. What you
would need to do is restart the agent on failure, but this time use the
verbose agent profile. The complete error message will now be in the
msrepl_Errors table. Errors are also logged in text files and dumped in
%WindDir%\system32 and will have an err extension. You can poll for them
using FileSystemWatcher.
4) You might want to poll msrepl_errors as discussed above and write to an
access database. Or you could fire something using the Replication Alert
Agent Failure.
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
> Hello there
> I some questions:
> 1. Iw'd like to run some store procedures before i'm starging the
> replication every day. Dose it enouth to add the task before the
> replication
> task?
> 2. If the replication faild i would like to make sure that none of the
> changes will be made.
> 3. IF an error occur can i nevegat it to the type of error:
> if the error is data error(validation of foreign key) or connection error.
> So i can give it order to run 5 minutes afterword?
> 4. Can i output the errors to outside files in order that other programs
> that use sql server (Access) can give a message to the user?
>
>
|||Whell Hilary
I've made an error in the replication and no MSRepl_Errors table were
created
After i got the error when i run the agent on the enterprise manager i
opened the query anlyser with the distribution database and i counldn't see
the table
When will the table appear?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYG7KczAGHA.272@.TK2MSFTNGP09.phx.gbl...
> 1) I would make these procs the first job step of your replication job
> 2) Double click on the job step, select advanced and have it run a job or
> script on failure.
> 3) This is a little tricky - the error should be in the msrepl_errors
> table in the distribution database where the agent is run. You can query
> it there, but the error may not be there depending on the error message.
> What you would need to do is restart the agent on failure, but this time
> use the verbose agent profile. The complete error message will now be in
> the msrepl_Errors table. Errors are also logged in text files and dumped
> in %WindDir%\system32 and will have an err extension. You can poll for
> them using FileSystemWatcher.
> 4) You might want to poll msrepl_errors as discussed above and write to an
> access database. Or you could fire something using the Replication Alert
> Agent Failure.
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
>
Subscribe to:
Posts (Atom)