1

I'm not sure if there is a debate about this.

When I read books, I'm advised to use triggers to follow up inserts into other tables. On the other hand, my mentor uses stored procedures to insert into the other tables.

My question here, which is the best method? Or is there a better way?

Joshua Rajandiran
  • 2,788
  • 7
  • 26
  • 53

2 Answers2

0

If You want of Insert data on table you can do it only by stored procedure not by triggers because Triggers can't accept parameters or anything at runtime.

you can call a stored procedure from inside another stored procedure but you can't directly call another trigger within a trigger.

So I think you should use Stored procedure rather then triggers

For Details you can visit to following link.

http://www.codeproject.com/Tips/624566/Differences-between-a-Stored-Procedure-and-a-Trigg

Please Reply for my answer.

Rohit Gupta
  • 455
  • 4
  • 16
  • An example of a trigger inserting into another table is a data change logging trigger that records all changes in one table in a logging table. – Nick.Mc May 19 '16 at 05:46
0

You don't need either. Start a transaction, make all your inserts (parent table first, child tables afterwards), end the transaction with COMMIT, and you are done.

Use stored procedures if you want to bundle this and ensure some kind of consistency (such that there is always at least one child table for a parent table for instance). But this can get complicated. Say you want to insert a new product with all its colors, sizes, suppliers and selling markets. Certain colors/sizes will be supplied by one or more suppliers and not the others, same with selling markets. To show these relations we usually use tables, but now you'll have to put these into parameters somehow in order to get them inserted into tables. I was told that some people have all their database writes in procedures. This is probably possible but has its limits.

As to auto-inserts by triggers: You can use them to log data, so as to get a history or the like, but you don't use them to insert business data. Think of an order with its positions: You insert the order header (order date, client number, ...), but how shall the trigger know which items were ordered? Or vice versa: You insert an order position (item number, price) and want the header be created automatically, but how shall the trigger know the client number? Triggers are not appropriate for such things.

As mentioned: Usually you'd just work with plain SQL in transactions and that's it.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • About the part about the stored procedures, if one would carefully design the structure of the database, then there wouldnt be a problem. In your example concerning the case where certain color/size of a product would be supplied by many suppliers, I don't need to worry about that since I'm using PostgreSQL and I can just make the `suppliers` column in json format and add new suppliers to the existing json data of suppliers. But anyway, based in your opinion, is this method acceptable for a development? – Joshua Rajandiran May 19 '16 at 09:05
  • My argument is when you have relations to insert, e.g. an item, its itemcolors, its itemsizes, its itemcolorsizes, its itemcolorsizesuppliers, etc., this is hard to represent as a tree, because its no hierarchical structure. Is a itemcolorsizes child to itemcolors or itemsizes? It's both, so this is no longer a hierarchical tree. This is why you use a database. It can be represented with relational objects and hence to JSON I guess, but that's complicated. It would be far easier to use mere insert statements rather than to have to build the relationally structured parameters for a ... – Thorsten Kettner May 19 '16 at 09:44
  • ... super insert procedure. Of course you could split this into separate functions fn_insert_item, fn_insert_itemcolor, fn_insert_itemsize, but then you are losing the advantage of the stored procedure over single inserts. This is an extreme example, however, only to show the limits. Yes, using stored procedures for writing into the database is a valid approach. And the richer the stored procedure support the better. With PL/pgSQL PostgreSQL offers a rich language as far as I am aware. So it seems like a good approach... – Thorsten Kettner May 19 '16 at 09:45
  • ... I don't know, however, how good PL/pgSQL is. In Oracle's PL/SQL you can have packages including the functions, so you really would have separate functions, one to add an item, one to add sizes to an item, one to add colors, etc., and then a final function to validate all that data and write it. So there would be no need for relational parameter structures. The package relates the single functions and let's you add data step by step. I don't know if such is possible with Postgre's PL/pgSQL. So my final answer should be: A very good approach for Oracle, maybe a good one for PostgreSQL, too. – Thorsten Kettner May 19 '16 at 09:46