triggers and stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

triggers and stored procedure

Author

Topic

j2dizzo
Starting Member

6 Posts

Posted - 2004-07-05 : 14:49:08

Hi,
can someone please help me with SQL Server triggers and stored procedures. I'm actually converting the sql queries from sybase syntax to T-SQL syntax but still can't get it to work. Here's the code

create trigger trUpdProduct
on Product
after update as
order 3 -- sybase sql syntax. Anyone know the equivalent in T-SQL?
-- I believe order 3 ensures that this trigger is fired as the third
referencing old as old_Pr new as new_Pr -- sybase sql syntax. I used inserted and deleted tables to replace this statement
-- but it seems not to work
-- Does anyone know what statement I can use to implement the update to every affected row?
begin
declare @i int
if (inserted.prSlot < deleted.productSlot)
begin
delete from SlotBank where (SlotBank.product = inserted.productId)and(SlotBank.slotNum > inserted.slotProduct)
end
else
declare @prodId int
set @proId = inserted.productId
set @i = deleted.productSlot
while (@i <= inserted.productSlot)
insert into SlotBank(slotProduct, slotNum) values(@proId,@i)
set @i = @i + 1
end

And here is one of the stored procedures

create procedure AccessAllSection
@p_Id int as
begin
delete from Access where ac_Product = p_Id
declare ThisSection cursor scroll dynamic for
select sec_Id as ThisSec_Id from Section
declare @sec_Id int
select @sec_Id = ThisSec_Id
insert into Access value(null,@p_Id,@sec_Id)
end

Thanks folks for taking a look at this problem and thanks in advance for your help.

j2dizzo

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 06:11:22

Blimey, the syntax is significantly different - considering they both came from the same stable.

I think the only question I can answer (and badly at that!) is the ORDER 3 one.

MSSQL doesn't have any ordering of triggers, except that you can specify the FIRST and LAST to execute with sp_settriggerorder.

I'll have a go at the Trigger too:

The TRIGGER looks as though it processes each row separately, which is not how MSSQL works (it processes them as a set, unless you use CURSOR or somesuch). Thus I would imageine your can replace:


if (inserted.prSlot < deleted.productSlot)
begin
delete from SlotBank 
where (SlotBank.product = inserted.productId)
      and(SlotBank.slotNum > inserted.slotProduct)
end

with


delete SB
from SlotBank SB
      JOIN inserted
           ON inserted.PK = SB.PK
      JOIN deleted
           ON deleted.PK = SB.PK
where (SlotBank.product = inserted.productId)
       and (SlotBank.slotNum > inserted.slotProduct)
       AND (inserted.prSlot < deleted.productSlot)

and replace


declare @prodId int
set @proId = inserted.productId
set @i = deleted.productSlot
while (@i <= inserted.productSlot)
insert into SlotBank(slotProduct, slotNum) values(@proId,@i)
set @i = @i + 1

with


insert into SlotBank(slotProduct, slotNum)
SELECT  inserted.productId,
        deleted.productSlot
from SlotBank SB
      JOIN inserted
           ON inserted.PK = SB.PK
      JOIN deleted
           ON deleted.PK = SB.PK
WHERE inserted.prSlot >= deleted.productSlot
      OR inserted.prSlot IS NULL -- May be irrelevant
      OR deleted.prSlot IS NULL  -- ditto

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 06:25:18

Hey Kristen, if I post all my stored procedures will you format them for me?

j2dizzo
Starting Member

6 Posts

Posted - 2004-07-06 : 13:15:16

Thanks kristen you have saved me a lot of time on trial and error. Do you maybe know how I can insert a value into a timestamp variable? An example is set below

CREATE TABLE ProductType
(
pro_Id int not null IDENTITY(1,1),
pro_Num timestamp not null,
pro_name varchar(30)
PRIMARY KEY (pro_Id)
)

INSERT INTO ProductType VALUES(??, 'This is name of product')

?? represent that I don't know what type of value to insert here for the timestamp datatype. I tried inserting 1 and 0 but I got an error and also when I try inserting values into the table only with the name of product.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-06 : 15:50:20

Not that I've ever used timestamp (anyone got a good reason to?)

But it kinda defeats the purpose if you could

BOL:

quote:

timestamp

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.


USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 timestamp)
GO

INSERT INTO myTable99(Col1) SELECT 1

SELECT * FROM myTable99
GO

INSERT INTO myTable99(Col1, Col2) SELECT 1, 2
GO

DROP TABLE myTable99
GO

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 21:13:20

> Hey Kristen, if I post all my stored procedures will you format them for me?

hehehe! Only if you can afford my rates!

I'm very dyslexic, so I often have to format code before I can make sense of it.

But that apart, I am also very fussy about formatting. We take pride in our code in my "shop", and the code is, in the main, beautifully formatted. So I don't see any reason to not do that when I post something here...

A lifetime of coding tells me that the time spent commenting and formatting when the code is first written saves SO much time downstream.

> how I can insert a value into a timestamp variable?

Well I don't think you can. SQL will CHANGE the value of a timestamp column every time the row is INSERTed or UPDATEd. (Suggest you use "rowversion" instead of "timestamp" as the later is deprecated as it conflicts with SQL-92 standard)

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 21:21:07

I used to use Oracle quite a bit. SQLNavigator from quest (A tool like EM/QA combined) had a code formatter that was very nice. It also had a tool called SQLLab that would generate alternate equivalent queries (oftem more efficient) that you could run and compare results. Another tool called Insight from Precise monitored the db and it would even generate alternate queries and run them for you comparing performance, recommend indexes, hints, etc. It was really an amazing piece of software - but for $80K for a single server license - it better be!

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 21:54:07

I could do with a decent Pretty Print tool for SQL ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources