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)
 sybase 7 and triggers

Author  Topic 

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-01-03 : 16:42:23

Hi Everyone...

I know sybase isn't really on this list, but t-sql is t-sql...

I have a single table, and everything seems to be working
ok, up till now...

Now the customer would like a smaller index table
and the current table to be the detail table.

To save a lot of middle layer coding, i am just going to
create a trigger on the (now) detail table, and when an
insert, update or delete happens, the detail table will
trigger and populate, delete or update the index table.

sounds simple, except when my old brain gets in the way...

How would i go about populating the index table with values
that are being passed to the detail table in the trigger???

thanks
tony

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-03 : 17:06:30
The two tables must have a PK in common.
This is for sqql server but you can convert it.


create trigger tr_mytbl on mytbl for insert, update, delete
as
if not exists (select * from deleted) -- insert or no op
insert indextbl select pk, col1, col2 from inserted
else if exists (select * from inserted) -- update
update indextbl
set col1 = i.col1 ,
col2 = i.col2
from indextbl t
join inserted i
on i.pk = t.pk
else -- delete
delete indextbl
from indextbl t
join deleted
on d.pk = t.pk


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -