Creating Trigger using While Loop

Hi All,
I am new to SQL 2000. I want to write a trigger using While statement. Where can I get the online user guide to learn about While statements in SQL2000. This will be helpful for me in my project and for future references.

Thanks in Advance
Vvragav.

Thanks and Regards,
vvragav

Hi,

It hardly makes any difference whether u r using While statement inside a Trigger or SP. It just depend on your requirement. It is just as any other language While loop. BOL has a topic "WHILE...BREAK or CONTINUE ".

Madhu

You have to realize that everything waits for the trigger to finish, before the action is completed. So any processing in the trigger must be done as quickly as possible.

The use of a WHILE loop is often an indicator that the programmer is new to T-SQL, and may not know the advantages of set-based processing.

There will always be situations where you can do certain things only in a WHILE loop, but in that case a proper stored procedure is usually a better approach than depending on a trigger to do the processing after the fact.

<br />Is this topic going to move to the SQL 2000 section? [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />Thanks,<br />DilliGrg

Now my previous post doesn't make sense after moving it to SQL 2000. [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Thanks,<br />DilliGrg

Hi Madhivanan,

1) I need to track the changes happening in TableA and appending the changes made in TableA into TableB.
2) I need to track only the changes that meet certain conditions i.e, before appending I need to check the changes whether it is equal to any of the rows in TableC (TableC contains 40 rows).
3) I can't predict how many changes will happen in TableA...
How to write a trigger for this...




Thanks and Regards,
vvragav

Check the CREATE TRIGGER syntax in Books Online ...

Inside the trigger, there are two "conceptual tables" that hold the "before" and "after" state of all rows that were inserted/updated/deleted.

These conceptual tables (snapshots) are called inserted and deleted, and you can include them in any SELECT statement within your trigger code. The only limitation is that you cannot refer to text, ntext and binary columns in the conceptual tables.

So the trigger on TableA might look something like this:

CREATE TRIGGER TableA_Insert ON dbo.TableA
FOR INSERT
AS
INSERT INTO dbo.TableB (B1, B2)
SELECT T.A1, T.A2
FROM dbo.TableA T
INNER JOIN inserted I ON T.<key> = I.<key>
GO

When a trigger fires for an insert action, there are only rows in inserted.
When a trigger fires for an update action, there are rows in both inserted and deleted.
When a trigger fires for a delete action, there are only rows in deleted.

Some client apps, like MS Access accessing SQL Server through ODBC-linked tables, will execute actions one row at a time, and the trigger will occur for each individual row. If SQL Server executes the action itself, the trigger fires once for all rows affected.

Do not use cursors in trigger code, as they will slow down the execution. Your main concern is that the insert/update/delete action on the table only finishes after the trigger has finished. Anyway, in 90% of cases the trigger code can be set-based instead of cursor-based.

Like I said, check Books Online for more fun features of triggers.