Hi,
I am beginner in SQL, so I am still learning, so apologies if this is something I should know.
Throughout the day new products are being created into our database (Adaptive Server Anywhere 9.0), and I was wanting to know the best way of having a stored procedure run when a new product is created.
I am not sure if I should be looking at Loops, Triggers, or something else all together.
If someone could help me out or point me in the direction of a good tutorial, that would be great.
Sounds like you need a Trigger. A Trigger will "fire" when a new row is inserted into the table (and/or when a row is Updated or Deleted - you can choose which events fire your trigger, or write separate triggers for some / all of the events).
Beware that your trigger must be able to handle multiple rows - I mention this because its a common misconception that the Trigger will fire for EACH row - it doesn't, sorry about that! Even if your system only ever creates new products one row at a time your trigger must still allow for the eventuality that multiple products are created, at some point in the future, in a single INSERT statement (even if the trigger action is just to Abort! if the rowcount > 1 )
You could use a Stored Procedure instead, but then every insert, wherever it might "come from", will have to be routed through that SProc. if you need to bulk insert lots of rows then an Sproc which can only handle one row at a time will be very inefficient, and might limit choices in the future. For example, even if at the moment you have a data entry form and someone fills that in to create new products, one-by-one, in the future you might have some other system which sends "new product requests" in batches, or you might want to build an "import from Excel" tool etc.
With relational databases you really (as in really REALLY!) want to try to avoid Loops, and instead process data by Sets - i.e. a single statement, such as INSERT, that applies to all rows in the set, rather than looping round and processing them row-by-agonising-row.
I think? that Adaptive Server Anywhere is a Sybase product? This forum is specifically for Microsoft SQL Server (which started out life as a Sybase product which Microsoft licensed ... but that was decades ago ...) so you might not find folk here who know much about other flavours of SQL.
Hi Kristen,
Thanks for the help, and the advice regarding ASA.
Can I ask how I would handle 'multiple rows', as I thought this is where I would use a loop.
In the Trigger you have a "pseudo table" called [inserted], it will contain one row for each of the rows in the INSERT statement.
Lets say you wanted to have an Audit Table to record a history of changes (on Insert and Update); you could create a trigger "AFTER INSERT, UPDATE" which copied ALL the rows from [inserted] into YourAuditTable.
e.g.
INSERT INTO MyAuditTable(Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM [inserted]
You would probably also have an "AFTER DELETE" trigger which recorded some data about any rows that were deleted (there is also a [deleted] pseudo table - in an UPDATE rows will exist in both [inserted] (the new version of the rows) and [deleted] (the original version of the rows).
What sort of post-processing are you wanting to do when a Product is created? Things which are slow and/or have interaction with external processes are best not done in Triggers - if the connection fails / stalls / runs slowly then there tends to be a lot of interference with other SQL statements / processes. For example, when we use a trigger to "send an Email" we just insert the email (To address, Subject, Body) into an "Email Queue" table and then have another process "pull" items out of that queue, and physically send them (or sit-and-wait until the Mail Server comes back online ... 
). The reason for that is so that the Trigger runs very quickly, and is not dependent on anything external. But we still use a Trigger to "catch" all changes in the table, and "prepare" the email.