how to get your trigger to catch the recordID when a specific record is updated

how can you make your trigger retrive a valuw from the record that fired the triger

I am trying to create a triger ( insert, update ) that will excute some stored procedures but the stored procedures need the record ID to function correctly
( I managed to to get the ID (generated outomaticaly when insert) by
SELECT MAX(recordID) From tableName
the problem is that when I update any record it always return the same ID regardless of what record is actually been updated and always return the ID of the last record inserted in the table instead

Have a read up on "SELECT @@IDENTITY" - it returns the last inserted ID....

ps - never use "select max()" to get the last inserted ID - you'll run into problems when you get other people using the db... e.g. if someone inserts a record before you make that call, you'll get the ID of their record, and not yours!

As stumpy mention, used select @@ IDENTITY, it will sort out your problem. As far as Select Max() is concerned then it will put you in more trouble under load.

SELECT MAX(ID) as new_id FROM <tblname> and then Insert into <tblname>(field1, field2.....) values(value1, value2...)

It doesn't work. Yes, read that again. The first insert would lock whatever page the insert occurred on. 99% of the time, this would make the SELECT MAX() successful.

However, under load, another process could do an insert that ended up on a new page and cause the subsequent MAX() to be incorrect for the first process. SQL 7 does row locking. So, the SELECT MAX() almost never works.

You can try it for yourself. If you're running on SQL7, try the following test. Create two files. Each file should insert into the same table as above. However, the first file should have a long (like 60 second) loop before the SELECT MAX() (but still in the transaction). Then, run the two files in two instances of a browser. output the ID from the SELECT MAX() in each file's query and you'll see (to your horror, I'm afraid) that they are the same.

sqlboy