HELP with Triggers [AGAIN]

Question

HELP with Triggers [AGAIN]

dB-226266

Ten Centuries

Points: 1298
More actions
May 13, 2005 at 4:07 am

#68995

I have proven w/o doubt that a trigger in sql will be fired for a single statement.. even if let's say it's a batch insert.. and also the fact that in Oracle, a trigger will be executed for each record in a batch statement.
Now here's the catch, I'm currently converting oracle trigger into SQL trigger, and inside the Oracle trigger is one insert statement.. an update to a logging table.
one of the values to be inserted is an incrementing value...(sequence in oracle)

How do I go about this.. since trigger in SQL is by batch(of records)?

Can anyone help me in my dilemma please.. huhuhu
----------------------------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

SQL Server maintains two special tables for triggers. The inserted and the deleted table. Do a search here how to deal with them. They can be JOINed like any other table. So you should easily be able to generate a sequence.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

dB-226266 Ten Centuries Points: 1298 More actions · Answer 2

Yeah I know but my problem is the increment part... hehehe.. I have made somewhat a short code for this and the trigger basically does this part..

Is this a good solution or is there a better one?

---------------------------------------------------------------------------

DECLARE @x TABLE

(

id numeric IDENTITY(1, 1),

value char(1)

)

--table where value is taken say the inserted table

DECLARE @y TABLE

(

id numeric, -- depends on the sequence

value char(1)

)

--table where value is saved some other logging table

DECLARE @temp TABLE

(

id numeric IDENTITY(1, 1),

value char(1)

)

--table where value is temporarily kept

DECLARE @n_Seq numeric -- the sequence used for inserting in @y

SET @n_Seq = 2 --starting sequence

select @n_Seq as 'starting sequence'

--dummy data

insert into @x values('a')

insert into @x values('b')

insert into @x values('c')

--get the wanted data from @x, and save it in @temp

insert into @temp

select value from @x

--then format the data using the start sequence insert into @y

insert into @y

select

@n_Seq + id,

value

from

@temp

--then lines here to update the sequence with the I did not include it but it's part of the functions... it's actually a table that contains.. sequencename and then the next_val data... and I made a procedure to get the next value given a sequence name but then I can'T use stored procedures w/n set statements can I? so I've come up w/ this solution.

SELECT @n_Seq = @n_Seq + COUNT(*) from @temp

--print results

select 'tablex', * from @x

select 'tabley', * from @y

select @n_Seq as 'last sequence', count(*) as 'records inserted in y' from @temp

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Why are you maintaining a sequence at all?

What will this be used for?

Usually this is a presentational issue.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

dB-226266 Ten Centuries Points: 1298 More actions · Answer 4

I'm not the one maintaining this.. I just follow what is asked of me.. and well convert the said triggers... into SQL..

dB-226266 Ten Centuries Points: 1298 More actions · Answer 5

After some thorough investigation of the code I aslo realize that some of the sequence is for the current file number. or some thing regarding cSV

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

I'm confused right now. Will this help you?

http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

The first example in the link can be extended to use some custom seed value like this:

use pubs

declare @i int

select @i=count(*) from authors

select rank=count(*)+@i, a1.au_lname, a1.au_fname

from authors a1, authors a2

where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname

group by a1.au_lname, a1.au_fname

order by 1

I hope this is at least a bit going your direction.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

dB-226266 Ten Centuries Points: 1298 More actions · Answer 7

One question though, What does the 'order by 1' part of the code do.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

'Order by 1' says to order the resultset by the first column in it, in this case, the count(*)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass