best practices stored procedures and triggers

Using SS2000 SP4. We have a .NET app that accesses the sql server database.
I've tried to keep all access via stored procedures and views and the user
only has execute permissions on the sp and select on the views.
The contractors have put a trigger on one of the tables.
CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyC
ontacts]
FOR INSERT, UPDATE
AS
IF (Select bitCompPrimaryContact From Inserted) = 1
Begin
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End
So, the user needs select and update permissions on the table. Since I
didn't want to give insert, update or delete permissions on any tables to th
e
user is there a way around it? What is a best practice approach. Do I just
not use update triggers?
Thanks,
--
Dan D.As long as you are using stored procedures you do not need to use this
trigger. Tell the contractors to put this code within the stored procedure
that updates the table tblCompanyContacts.
Keith Kratochvil
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> Using SS2000 SP4. We have a .NET app that accesses the sql server
> database.
> I've tried to keep all access via stored procedures and views and the user
> only has execute permissions on the sp and select on the views.
> The contractors have put a trigger on one of the tables.
> CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompan
yContacts]
> FOR INSERT, UPDATE
> AS
> IF (Select bitCompPrimaryContact From Inserted) = 1
> Begin
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End
> So, the user needs select and update permissions on the table. Since I
> didn't want to give insert, update or delete permissions on any tables to
> the
> user is there a way around it? What is a best practice approach. Do I just
> not use update triggers?
> Thanks,
> --
> Dan D.|||That sounds good. Thanks Keith.
--
Dan D.
"Keith Kratochvil" wrote:

> As long as you are using stored procedures you do not need to use this
> trigger. Tell the contractors to put this code within the stored procedur
e
> that updates the table tblCompanyContacts.
> --
> Keith Kratochvil
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
>
>|||I don't believe you don't need to grant any additional permissions on
the base table to users to execute a trigger. The "grant execute" on
the update or insert stored procedures should be enough.
Dan D. wrote:[vbcol=seagreen]
> That sounds good. Thanks Keith.
> --
> Dan D.
>
> "Keith Kratochvil" wrote:
>|||On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:

>Using SS2000 SP4. We have a .NET app that accesses the sql server database.
>I've tried to keep all access via stored procedures and views and the user
>only has execute permissions on the sp and select on the views.
>The contractors have put a trigger on one of the tables.
>CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompany
Contacts]
>FOR INSERT, UPDATE
>AS
>IF (Select bitCompPrimaryContact From Inserted) = 1
>Begin
>Update tblCompanyContacts Set bitCompPrimaryContact = 0
>Where numCompanyId = (Select numCompanyId From Inserted)
>And numContactID <> (Select numContactId From Inserted)
>End
Hi Dan,
First, fire the contractors. This trigger will fail on any multi-row
insert or update. No-one should write triggers like that and be paid for
it!

>So, the user needs select and update permissions on the table.
Says who? As long as the table is owned by the same user that owns the
trigger, the user doesn't need any permissions on the table - see the
repro script below.

> Since I
>didn't want to give insert, update or delete permissions on any tables to t
he
>user is there a way around it? What is a best practice approach. Do I just
>not use update triggers?
In most cases, I would recommend having all objects (tables, views,
stored procedures, triggers, functions, ...) owned by dbo. Then, you can
set permissions exactly as required. Due to all objects having the same
owner, all statements from within stored procedures, triggers, and
functions will work. (Unless you're using dynamic SQL).
Here's a repro to show that a user doesn't need permission on the table
if a trigger changes data in the table.
First, run the script below while connected with an accoount with
sufficient rights to add databases and logins:
USE master
go
DROP DATABASE test
go
sp_droplogin @.loginame = 'test'
go
CREATE DATABASE test
go
sp_addlogin @.loginame = 'test',
@.passwd = 'test',
@.defdb = 'test'
go
USE test
go
sp_grantdbaccess @.loginame = 'test'
go
CREATE TABLE tab (a int NOT NULL,
b int DEFAULT NULL)
go
CREATE VIEW vw
AS
SELECT a, b
FROM tab
go
CREATE TRIGGER trig
ON tab
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT > 0
BEGIN
UPDATE tab
SET b = a + 15
WHERE b IS NULL
AND EXISTS
(SELECT *
FROM inserted
WHERE inserted.a = tab.a)
END
go
CREATE PROC ins_tab (@.a int)
AS
INSERT INTO tab (a)
VALUES (@.a)
go
DENY ALL ON tab TO test
DENY UPDATE, INSERT, DELETE ON vw TO test
GRANT SELECT ON vw TO test
GRANT EXECUTE ON ins_tab TO test
go
Then, open a new connection, log in with userid 'test' (use SQL Server
authentication, not integrated authentication) and execute the code
below:
-- Login as user 'test'
-- Fails - no permissions on tab
SELECT *
FROM tab
-- Succeeds - SELECT permission on vw granted
SELECT *
FROM vw
-- Fails - no INSERT permission on vw
INSERT INTO vw (a)
VALUES (3)
-- Succees - EXECUTE permission on ins_tab granted
EXECUTE ins_tab @.a = 3
-- Check that trigger has executed as well
SELECT *
FROM vw
go
Here's the output:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
a b
-- --
Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
a b
-- --
3 18
Hugo Kornelis, SQL Server MVP|||I see it now. Thanks for such a detailed explanation Hugo.
BTW, we did fire the contractors.
--
Dan D.
"Hugo Kornelis" wrote:

> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
>
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
>
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fir
e
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:

> The trigger they wrote also contained a rookie mistake. It was designed fo
r
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This co
de
> will raise an error if there is more than one row in the Inserted table (i
n
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or inser
t
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in the
ir
> procs. If you do put it in your procs, you will need to put that logic int
o
> every proc that will update or insert into that table. If this is a large
#
> of procs then you are setting up a shotgun surgery approach to maintenance
.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want t
he
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...[vbcol=seagreen]
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under contro
l.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, th
e
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:

> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
>
>

best practices stored procedures and triggers

Using SS2000 SP4. We have a .NET app that accesses the sql server database.
I've tried to keep all access via stored procedures and views and the user
only has execute permissions on the sp and select on the views.
The contractors have put a trigger on one of the tables.
CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
FOR INSERT, UPDATE
AS
IF (Select bitCompPrimaryContact From Inserted) = 1
Begin
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End
So, the user needs select and update permissions on the table. Since I
didn't want to give insert, update or delete permissions on any tables to the
user is there a way around it? What is a best practice approach. Do I just
not use update triggers?
Thanks,
--
Dan D.As long as you are using stored procedures you do not need to use this
trigger. Tell the contractors to put this code within the stored procedure
that updates the table tblCompanyContacts.
--
Keith Kratochvil
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> Using SS2000 SP4. We have a .NET app that accesses the sql server
> database.
> I've tried to keep all access via stored procedures and views and the user
> only has execute permissions on the sp and select on the views.
> The contractors have put a trigger on one of the tables.
> CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> FOR INSERT, UPDATE
> AS
> IF (Select bitCompPrimaryContact From Inserted) = 1
> Begin
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End
> So, the user needs select and update permissions on the table. Since I
> didn't want to give insert, update or delete permissions on any tables to
> the
> user is there a way around it? What is a best practice approach. Do I just
> not use update triggers?
> Thanks,
> --
> Dan D.|||That sounds good. Thanks Keith.
--
Dan D.
"Keith Kratochvil" wrote:
> As long as you are using stored procedures you do not need to use this
> trigger. Tell the contractors to put this code within the stored procedure
> that updates the table tblCompanyContacts.
> --
> Keith Kratochvil
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > database.
> > I've tried to keep all access via stored procedures and views and the user
> > only has execute permissions on the sp and select on the views.
> >
> > The contractors have put a trigger on one of the tables.
> >
> > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > FOR INSERT, UPDATE
> > AS
> > IF (Select bitCompPrimaryContact From Inserted) = 1
> > Begin
> > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > Where numCompanyId = (Select numCompanyId From Inserted)
> > And numContactID <> (Select numContactId From Inserted)
> > End
> >
> > So, the user needs select and update permissions on the table. Since I
> > didn't want to give insert, update or delete permissions on any tables to
> > the
> > user is there a way around it? What is a best practice approach. Do I just
> > not use update triggers?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||I don't believe you don't need to grant any additional permissions on
the base table to users to execute a trigger. The "grant execute" on
the update or insert stored procedures should be enough.
Dan D. wrote:
> That sounds good. Thanks Keith.
> --
> Dan D.
>
> "Keith Kratochvil" wrote:
> > As long as you are using stored procedures you do not need to use this
> > trigger. Tell the contractors to put this code within the stored procedure
> > that updates the table tblCompanyContacts.
> >
> > --
> > Keith Kratochvil
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > > database.
> > > I've tried to keep all access via stored procedures and views and the user
> > > only has execute permissions on the sp and select on the views.
> > >
> > > The contractors have put a trigger on one of the tables.
> > >
> > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > > FOR INSERT, UPDATE
> > > AS
> > > IF (Select bitCompPrimaryContact From Inserted) = 1
> > > Begin
> > > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > > Where numCompanyId = (Select numCompanyId From Inserted)
> > > And numContactID <> (Select numContactId From Inserted)
> > > End
> > >
> > > So, the user needs select and update permissions on the table. Since I
> > > didn't want to give insert, update or delete permissions on any tables to
> > > the
> > > user is there a way around it? What is a best practice approach. Do I just
> > > not use update triggers?
> > >
> > > Thanks,
> > > --
> > > Dan D.
> >
> >
> >|||On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>Using SS2000 SP4. We have a .NET app that accesses the sql server database.
>I've tried to keep all access via stored procedures and views and the user
>only has execute permissions on the sp and select on the views.
>The contractors have put a trigger on one of the tables.
>CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
>FOR INSERT, UPDATE
>AS
>IF (Select bitCompPrimaryContact From Inserted) = 1
>Begin
>Update tblCompanyContacts Set bitCompPrimaryContact = 0
>Where numCompanyId = (Select numCompanyId From Inserted)
>And numContactID <> (Select numContactId From Inserted)
>End
Hi Dan,
First, fire the contractors. This trigger will fail on any multi-row
insert or update. No-one should write triggers like that and be paid for
it!
>So, the user needs select and update permissions on the table.
Says who? As long as the table is owned by the same user that owns the
trigger, the user doesn't need any permissions on the table - see the
repro script below.
> Since I
>didn't want to give insert, update or delete permissions on any tables to the
>user is there a way around it? What is a best practice approach. Do I just
>not use update triggers?
In most cases, I would recommend having all objects (tables, views,
stored procedures, triggers, functions, ...) owned by dbo. Then, you can
set permissions exactly as required. Due to all objects having the same
owner, all statements from within stored procedures, triggers, and
functions will work. (Unless you're using dynamic SQL).
Here's a repro to show that a user doesn't need permission on the table
if a trigger changes data in the table.
First, run the script below while connected with an accoount with
sufficient rights to add databases and logins:
USE master
go
DROP DATABASE test
go
sp_droplogin @.loginame = 'test'
go
CREATE DATABASE test
go
sp_addlogin @.loginame = 'test',
@.passwd = 'test',
@.defdb = 'test'
go
USE test
go
sp_grantdbaccess @.loginame = 'test'
go
CREATE TABLE tab (a int NOT NULL,
b int DEFAULT NULL)
go
CREATE VIEW vw
AS
SELECT a, b
FROM tab
go
CREATE TRIGGER trig
ON tab
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT > 0
BEGIN
UPDATE tab
SET b = a + 15
WHERE b IS NULL
AND EXISTS
(SELECT *
FROM inserted
WHERE inserted.a = tab.a)
END
go
CREATE PROC ins_tab (@.a int)
AS
INSERT INTO tab (a)
VALUES (@.a)
go
DENY ALL ON tab TO test
DENY UPDATE, INSERT, DELETE ON vw TO test
GRANT SELECT ON vw TO test
GRANT EXECUTE ON ins_tab TO test
go
Then, open a new connection, log in with userid 'test' (use SQL Server
authentication, not integrated authentication) and execute the code
below:
-- Login as user 'test'
-- Fails - no permissions on tab
SELECT *
FROM tab
-- Succeeds - SELECT permission on vw granted
SELECT *
FROM vw
-- Fails - no INSERT permission on vw
INSERT INTO vw (a)
VALUES (3)
-- Succees - EXECUTE permission on ins_tab granted
EXECUTE ins_tab @.a = 3
-- Check that trigger has executed as well
SELECT *
FROM vw
go
Here's the output:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
a b
-- --
Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
a b
-- --
3 18
Hugo Kornelis, SQL Server MVP|||I see it now. Thanks for such a detailed explanation Hugo.
BTW, we did fire the contractors.:)
--
Dan D.
"Hugo Kornelis" wrote:
> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
> >Using SS2000 SP4. We have a .NET app that accesses the sql server database.
> >I've tried to keep all access via stored procedures and views and the user
> >only has execute permissions on the sp and select on the views.
> >
> >The contractors have put a trigger on one of the tables.
> >
> >CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> >FOR INSERT, UPDATE
> >AS
> >IF (Select bitCompPrimaryContact From Inserted) = 1
> >Begin
> >Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >Where numCompanyId = (Select numCompanyId From Inserted)
> >And numContactID <> (Select numContactId From Inserted)
> >End
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
> >So, the user needs select and update permissions on the table.
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
> > Since I
> >didn't want to give insert, update or delete permissions on any tables to the
> >user is there a way around it? What is a best practice approach. Do I just
> >not use update triggers?
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fire
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:
> The trigger they wrote also contained a rookie mistake. It was designed for
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This code
> will raise an error if there is more than one row in the Inserted table (in
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or insert
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in their
> procs. If you do put it in your procs, you will need to put that logic into
> every proc that will update or insert into that table. If this is a large #
> of procs then you are setting up a shotgun surgery approach to maintenance.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want the
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>> The trigger they wrote also contained a rookie mistake. It was designed
>> for
>> a single row at a time. When you are writing a trigger you always need to
>> plan on having multi row events occur at some point.
>> Snip--
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> Where numCompanyId = (Select numCompanyId From Inserted)
>> And numContactID <> (Select numContactId From Inserted)
>> End snip--
>> See how the subqueries assume that only one item will be returned. This
>> code
>> will raise an error if there is more than one row in the Inserted table
>> (in
>> other words, if a multi row update or insert occurs) and that would cause
>> the original transaction to be rolled back.
>> Far better would be the following:
>> ---
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> FROM tblCompanyContacts C
>> JOIN Inserted I
>> ON I.numCompanyId = C.numCompanyId
>> And I.numContactID <> C.numContactId
>> ---
>> This way no matter how many rows have been affected by the update or
>> insert
>> statement the trigger will now handle them.
>> All of the advice you received on moving the logic to the stored
>> procedure
>> has merit. You should understand the following. The trigger will fire for
>> any update or insert on your table. Some folks like to use triggers to
>> handle some of their data integrity enforcement and not worry about in
>> their
>> procs. If you do put it in your procs, you will need to put that logic
>> into
>> every proc that will update or insert into that table. If this is a large
>> #
>> of procs then you are setting up a shotgun surgery approach to
>> maintenance.
>> The other thing to be aware of is that your trigger fires as part of your
>> original transaction, so an error in it will cause your original action
>> to
>> be rolled back, as well as the locks from your original action are still
>> maintained during the trigger. In this case that is not a bad thing,
>> since
>> you want this to happen as part of the update, and if it fails, you want
>> the
>> update to fail as well.
>> --
>> David Lundell
>> Principal Consultant and Trainer
>> www.MutuallyBeneficial.com
>> David@.MutuallyBeneficial.com
>>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under control.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, the
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:
> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> > So, is it probably safer to stay away from subqueries in triggers?
> >
> > One of the things we have to be careful about with triggers is that we do
> > a
> > lot of data manipulation on the backend. So we have to be sure that if we
> > insert 5M rows on the backend and not through the app, a trigger doesn't
> > fire
> > and do something it shouldn't.
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "David Lundell" wrote:
> >
> >> The trigger they wrote also contained a rookie mistake. It was designed
> >> for
> >> a single row at a time. When you are writing a trigger you always need to
> >> plan on having multi row events occur at some point.
> >>
> >> Snip--
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> Where numCompanyId = (Select numCompanyId From Inserted)
> >> And numContactID <> (Select numContactId From Inserted)
> >> End snip--
> >>
> >> See how the subqueries assume that only one item will be returned. This
> >> code
> >> will raise an error if there is more than one row in the Inserted table
> >> (in
> >> other words, if a multi row update or insert occurs) and that would cause
> >> the original transaction to be rolled back.
> >>
> >> Far better would be the following:
> >> ---
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> FROM tblCompanyContacts C
> >> JOIN Inserted I
> >> ON I.numCompanyId = C.numCompanyId
> >> And I.numContactID <> C.numContactId
> >> ---
> >> This way no matter how many rows have been affected by the update or
> >> insert
> >> statement the trigger will now handle them.
> >>
> >> All of the advice you received on moving the logic to the stored
> >> procedure
> >> has merit. You should understand the following. The trigger will fire for
> >> any update or insert on your table. Some folks like to use triggers to
> >> handle some of their data integrity enforcement and not worry about in
> >> their
> >> procs. If you do put it in your procs, you will need to put that logic
> >> into
> >> every proc that will update or insert into that table. If this is a large
> >> #
> >> of procs then you are setting up a shotgun surgery approach to
> >> maintenance.
> >> The other thing to be aware of is that your trigger fires as part of your
> >> original transaction, so an error in it will cause your original action
> >> to
> >> be rolled back, as well as the locks from your original action are still
> >> maintained during the trigger. In this case that is not a bad thing,
> >> since
> >> you want this to happen as part of the update, and if it fails, you want
> >> the
> >> update to fail as well.
> >> --
> >> David Lundell
> >> Principal Consultant and Trainer
> >> www.MutuallyBeneficial.com
> >> David@.MutuallyBeneficial.com
> >>
> >>
> >>
>
>|||On Tue, 23 May 2006 06:07:02 -0700, Dan D. wrote:
>So, is it probably safer to stay away from subqueries in triggers?
Hi Dan,
Only in the same way that not using a database at all prevents errors :)
Use subqueries if you need them - just be aware that a trigger will
always fires exactly once when a corresponding INSERT, UPDATE or DELETE
statement is executed. Design your triggers to work regardless of number
of rows affected. And test them with single-row, multi-row and zero-row
operations.
>One of the things we have to be careful about with triggers is that we do a
>lot of data manipulation on the backend. So we have to be sure that if we
>insert 5M rows on the backend and not through the app, a trigger doesn't fire
>and do something it shouldn't.
Bottom line when deciding if something belongs in a stored proc or in a
trigger is: is in integrity related?
Triggers should be used to enforce the integrity rules that can't be
enforced with standard SQL. For instance complicated constraints that
can't be exppressed in SQL constraints. Or computations that (for
whatever reason) can not be calculated in a view. The execution of a
trigger is about as hard to bypass as the checking of a constraint or
the "updating" of a calculated value in a view. That's because it should
be used for code that really should never be bypassed.
Bypassing the execution of stored procedures is much easier. Exactly how
easy depends on yoour security model as well - but there are always SOME
users able to update a table directly without executing the stored proc
that should be used to update the table.
--
Hugo Kornelis, SQL Server MVP

Best practice - triggers and SP

HI all,
Whats the best practice when it comes to deciding what to put into triggers
or SP.
At the moment I find myself putting a lot of stuff into triggers. I have no
idea as to what is generally considered too much for triggers.
Any thoughts
Thanks
RobertRobert
http://www.sql-server-performance.c..._procedures.asp
http://www.sql-server-performance.c...gger_tuning.asp
"Robert Bravery" <me@.u.com> wrote in message
news:OkNr$tpRGHA.4920@.tk2msftngp13.phx.gbl...
> HI all,
> Whats the best practice when it comes to deciding what to put into
> triggers
> or SP.
> At the moment I find myself putting a lot of stuff into triggers. I have
> no
> idea as to what is generally considered too much for triggers.
> Any thoughts
> Thanks
> Robert
>|||Personally, I put as little as possible in the triggers. Use SP if you can.
I use triggers when there is a possibility of accessing data in the table
from a number of different procedures or when I need to be certain
(absolutely) that something will be done when data changes in the table.
MC
"Robert Bravery" <me@.u.com> wrote in message
news:OkNr$tpRGHA.4920@.tk2msftngp13.phx.gbl...
> HI all,
> Whats the best practice when it comes to deciding what to put into
> triggers
> or SP.
> At the moment I find myself putting a lot of stuff into triggers. I have
> no
> idea as to what is generally considered too much for triggers.
> Any thoughts
> Thanks
> Robert
>|||I find triggers are very good to do audit, in other cases SP is preferred.
James
"Robert Bravery" wrote:

> HI all,
> Whats the best practice when it comes to deciding what to put into trigger
s
> or SP.
> At the moment I find myself putting a lot of stuff into triggers. I have n
o
> idea as to what is generally considered too much for triggers.
> Any thoughts
> Thanks
> Robert
>
>|||Triggers are best for enforcing constraints that cannot be enforced
declaratively, or for tracking or reacting to modifications--especially from
multiple sources.
Stored procedures are better for everything else.
"Robert Bravery" <me@.u.com> wrote in message
news:OkNr$tpRGHA.4920@.tk2msftngp13.phx.gbl...
> HI all,
> Whats the best practice when it comes to deciding what to put into
> triggers
> or SP.
> At the moment I find myself putting a lot of stuff into triggers. I have
> no
> idea as to what is generally considered too much for triggers.
> Any thoughts
> Thanks
> Robert
>

Begin and Commit Transaction statements in a trigger

I am trying to tidy up a bunch of triggers on a legacy database. The
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
Paddy
Paddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>
|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
Thanks, Liliya
|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
. I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.
|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.
|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go
|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
|||RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF

Begin and Commit Transaction statements in a trigger

I am trying to tidy up a bunch of triggers on a legacy database. The
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
PaddyPaddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
:). I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
--
Thanks, Liliya|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT|||TEST2
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _trn_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
select * from master.dbo.syslockinfo
exec sp_lock
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exe
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit|||id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000|||----
sp_lock in sp exec @.val= _nval_test call and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
get in in sp DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read uncommitted
(14 row(s) affected)
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||--
sp_lock in sp at the end
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:3 0x0009050098BD01000100030000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:4 0x0009050098BD01000100040000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 5 126623494 0 RID 1:114072:3 X GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 126623494 0 RID 1:114072:4 X GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
NEXTVAL
--
6|||--
sp_lock in sp exit and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 1 85575343 0 TAB IS GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
2 2 _trn_test in sp _nval_test
1 2 _trn_test in sp begin next tran, audit
1 1 _trn_test in sp after SET IMPLICIT_TRANSACTIONS OFF
0 NULL _trn_test begin script,after exe sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
Thanks, Liliya