Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

lock table for trigger update?

2 posts in General Discussion Last posting was on 2012-11-29 21:34:03.0Z
rick_806 Posted on 2012-11-29 21:02:13.0Z
Sender: 6ad2.50b7b4fe.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.general
Subject: lock table for trigger update?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50b7cd55.6f27.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 29 Nov 2012 13:02:13 -0800
X-Trace: forums-1-dub 1354222933 172.20.134.41 (29 Nov 2012 13:02:13 -0800)
X-Original-Trace: 29 Nov 2012 13:02:13 -0800, 172.20.134.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31560
Article PK: 74449

ASE 15.5 ESD4 I need to replace a very large trigger on a
hot table. I am looking at doing it as a transaction with
"lock table". Is this going to work/good idea? Since the
table has IUD rates of up to 100 per second, I wnat to
eliminate the possibility that a record will be written to
the table between the drop trigger & create trigger
commands.

thx,
rick_806


Bret Halford Posted on 2012-11-29 21:34:03.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: lock table for trigger update?
References: <50b7cd55.6f27.1681692777@sybase.com>
In-Reply-To: <50b7cd55.6f27.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50b7d4cb@forums-1-dub>
Date: 29 Nov 2012 13:34:03 -0800
X-Trace: forums-1-dub 1354224843 172.20.134.152 (29 Nov 2012 13:34:03 -0800)
X-Original-Trace: 29 Nov 2012 13:34:03 -0800, vip152.sybase.com
Lines: 78
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31561
Article PK: 74450


On 11/29/2012 2:02 PM, rick_806 wrote:
> ASE 15.5 ESD4 I need to replace a very large trigger on a
> hot table. I am looking at doing it as a transaction with
> "lock table". Is this going to work/good idea? Since the
> table has IUD rates of up to 100 per second, I wnat to
> eliminate the possibility that a record will be written to
> the table between the drop trigger & create trigger
> commands.
>
> thx,
> rick_806
>

Hi Rick,

Normally DDL isn't allowed within transactions,
and LOCK TABLE requires being in a transaction.
You could enable "ddl in tran" to get around that.
But in isn't necessary in the case of triggers.

Triggers have an interesting property that if you
have a trigger on a table, you don't have to drop it
before you create another trigger - if you issue
a create trigger, ASE will (silently) replace the
existing trigger.

1> create table mytab(c1 int)
2> go
1> create trigger mytrig on mytab for insert as print "hi"
2> go
1> insert mytab values (2)
2> go
hi
(1 row affected)
1> create trigger mytrig2 on mytab for insert as print "bye"
2> go
1> insert mytab values (1)
2> go
bye
(1 row affected)

ASE takes a table level lock during the create trigger command.
(you can see that on a test server if you turn on traceflag 1212
to see all the locks granted and released while you run the
create trigger command).

So all you have to do is just create the new trigger, ASE locks
the table for you.

1> dbcc traceon(3604)
2> go
1> dbcc traceon(1212)
2> go
1> create trigger tr2 on tt for insert as print "bye"
2> go
lock_logical shared intent db=7 obj=4 pg=0 row=0 ctx=0x0 suff=0 pspid=11
fileindex=52 (crp.c) lineno=226 LOCK_GRANTED
lock_release shared intent db=7 obj=0 pg=4 row=0 splk=0x140680 ctx=0x0
suff=0
fileindex=52 (crp.c) lineno=226
lock_multiple exclusive table db=7 obj=672002394 splk=0x140680 suff=0
fileindex=241 (proccreate.c) lineno=4587 LOCK_GRANTED
lock_multiple exclusive table db=7 obj=656002337 splk=0x140680 suff=0
fileindex=241 (proccreate.c) lineno=4587 LOCK_GRANTED

****** 640002280 is the objid of table tt ******
lock_multiple shared table db=7 obj=640002280 splk=0x140680 suff=0
fileindex=241
(proccreate.c) lineno=4587 LOCK_GRANTED


lock_logical exclusive intent db=7 obj=6 pg=0 row=0 ctx=0x1 suff=0 pspid=11
fileindex=113 (fill.c) lineno=218 LOCK_GRANTED
lock_logical exclusive row db=7 obj=6 pg=225 row=9 ctx=0x81 suff=0 pspid=11
[...]


-bret