Solved

Set Trigger Order

Posted on 2002-06-10
11
374 Views
Last Modified: 2010-05-18
A co-worker was telling me there is a stored procedure called sp_SetTriggerOrder supposedly in the master database. He is trying to figure out the parameters that are passed into it. However he can not find it in the master database. Does anybody know where this is located?

Lmred
0
Comment
Question by:lmred
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7068258
From MSDN:
<quote>
sp_settriggerorder
Specifies which AFTER triggers associated with a table will be fired first or last. The AFTER triggers that will be fired between the first and last triggers will be executed in undefined order.

Syntax
sp_settriggerorder[@triggername = ] 'triggername'
    , [@order = ] 'value'
    , [@stmttype = ] 'statement_type'

Argument
[@triggername = ] 'triggername'

Is the name of the trigger whose order will be set or changed. triggername is sysname. If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure will return an error.

[@order = ] 'value'

Is the setting for the new trigger order. value is varchar(10) and it can be any of the following values.



Important  The First and Last triggers must be two different triggers.


Value Description
First Trigger will be fired first.
Last Trigger will be fired last.
None Trigger will be fired in undefined order.


[@stmttype = ] 'statement_type'

Specifies which SQL statement fires the trigger. statement_type is varchar(10) and can be INSERT, UPDATE, or DELETE. A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. SQL Server will return an error if TR1, which has been defined only as an INSERT trigger, is set as a First (or Last) trigger for an UPDATE statement. For more information, see the Remarks section.

Return Code Values
0 (success) and 1 (failure)

Remarks
There can be only one First and one Last trigger for each of INSERT, UPDATE, or DELETE statement on a single table.

If a First trigger is already defined on the table, you cannot designate a new trigger as First for the same table for the same operation (INSERT, UPDATE, or DELETE). This restriction also applies Last triggers.

As part of SQL Server replication, a First trigger can be designated on a published table; however, if a conflict with the user-defined trigger exists, the designation of the user-defined trigger must be changed to None before the table can be published.

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset with sp_settriggerorder.

If the same trigger has to be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for the statement type.

Permissions
The owner of the trigger and the table on which the trigger is defined has execute permissions for sp_settriggerorder. Members of db_owner and db_ddladmin roles in the current database, as well as the sysadmin server role can execute this stored procedure.

Examples
sp_settriggerorder @triggername= 'MyTrigger', @order='first', @stmttype = 'UPDATE'

</quote>
0
 
LVL 1

Author Comment

by:lmred
ID: 7068305
When this syntax is placed in SQL Server Analyzer (we are using 2000), it does not run. It says it can not find this stored procedure. Even when you put master..spSetTriggerOrder it does not run.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7068373
This sounds like a permissions problem.

Anthony
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7068403
Be sure to code sp_SetTriggerOrder rather than spSetTriggerOrder.
0
 
LVL 1

Author Comment

by:lmred
ID: 7069657
The underscore has been placed in the name of the stored procedure.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7069892
I think acperkins is right, it does sound like a permissions problem.  

You can use EM to verify that the SP exists in master, then right click on it, select Properties, click on Permissions..., and verify that Public has EXEC authority (that appears to be the default).
0
 
LVL 1

Author Comment

by:lmred
ID: 7069904
It does not show up in master.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 50 total points
ID: 7070046
Are you looking at a 7.0 server?  As you know, that SP does not exist on 7.0.  But that SP should be there, in the master db, on a 2000 server.  If it's not, I would suggest you back up the databases and re-install SQL 2K.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7070047
Are you using SQL Server 7?

I believe the sp_SetTriggerOrder was introduced with SQL Server 2000.

Anthony
0
 
LVL 1

Author Comment

by:lmred
ID: 7162736
Yes, I am looking at SQL 7. That does explain it. Thanks.


Lisa
0
 
LVL 1

Author Comment

by:lmred
ID: 7162740
Thanks everybody for your help!

Lisa
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question