Hello. I need help with some triggers.
I have a table with primary key containing integer fileds.
(IE id, alternate and version)
The table also have one filed "active" that tells which record is active. Only one
record could be active for each ID.
What I want to have is two triggers.
if I delete the ID that is active I need to automaticly set one of the other to active.
IE the one with Highest version and min alternate.
ID 1 Alt 1 Ver 1 Active 0
ID 1 Alt 1 Ver 2 Active 0
ID 1 Alt 2 Ver 2 Active 1
ID 1 Alt 1 Ver 3 Active 0
ID 1 Alt 2 Ver 3 Active 0
IF the row with activer =1 are deleted
ID 1 Alt 1 Ver 3 should be flagged as active.
Then the insert / update trigger.
If a new ID is added and set to active (or an old row is flagged as active)
all other with the same ID should automaticly be flagged with active=0
Can someone please help me with this?
I need the code to be SQL 2000 compatible.