Prev: ALTER TABLE statements showing up in my stored procedures
Next: Operand type clash: int is incompatible with ntext
From: Bob on 11 Aug 2010 20:34
Hello folks, I have not posted for a while, I hope this group is as
good as it used to be.

I want to write a trigger that performs an insert or update or delete
on another table.

The trigger would fire on insert, update, or delete.

The table has a pk field called idx.

So after the sql statement is completed, the trigger needs to be able
to get the pk (value) that was just added, updated, or deleted.

Assume I have a table of idx (pk), num (int (not unique)), Fruit
(char20)

I need the trigger to get the idx...then i will get the num,,,then I
will create a cursor that "rolls up" and concantenates all the
"Fruits" with that num which I will then insert into another table.

Anybody got an (good) ideas on how I can do this.

Thanks in advance,

Bob Sweeney







From: --CELKO-- on 13 Aug 2010 13:33
I think you are missing a few things and are taking the wrong
approach.

Triggers are proceural code and should be avoided in SQL as much as
possible in favor of constriants and other declarative code. My
hueristic is that your sodul not write more than five of them in your
entire career.

Your second table is in violation of First Normal Form and it is full
of computed, formatted data. Neither of these things is good, unless
you can prove that Dr. Codd was wrong :)

I will guess that is a product heirarchy from your narrative. I would
have 'Fruits' as a category with 'Apples' subordinated to it; "Granny
Smith', 'MacIntosh' and 'Red Delicious' subordinated to the apples,
etc. Is this right?

This rollup should be done in a VIEW that is always current and only
constructed when it is needed. But you seem to be stuck in a "file
system mindset" (you even use the term "field" instead of column!)
where there are no virtual data elements, applications and data are
welded together and you have variant records.

Google the nested sets model and see if it helps. When you insert,
update and delete from a nested sets hierarchy, the VIEWS on it
automatically change. The procedures to maintain it are simple.


 | 
Pages: 1
Prev: ALTER TABLE statements showing up in my stored procedures
Next: Operand type clash: int is incompatible with ntext