Help with trigger

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.

1. Delete.

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.

IE

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
 the row
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.
pucko73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
try this

CREATE TRIGGER TrgDel ON [tablename]
FOR DELETE
AS

IF (SELECT active FROM deleted) = 1
BEGIN

Update
active =1
where Alternate  = ( select min(alternate) from [tablename] T
 inner join deleted D on T.ID = D.ID )
and version = (select max(version) from [tablename] T
 inner join deleted D on T.ID = D.ID)

END
0
Pratima PharandeCommented:
CREATE TRIGGER TrgDel ON [tablename]
FOR DELETE
AS

IF (SELECT active FROM deleted) = 1
BEGIN

Update [tablename]
active =1
where Alternate  = ( select min(alternate) from [tablename] T
 inner join deleted D on T.ID = D.ID )
and version = (select max(version) from [tablename] T
 inner join deleted D on T.ID = D.ID)

END
0
Pratima PharandeCommented:
CREATE TRIGGER TrgINS ON [tablename]
FOR Insert
AS

IF (SELECT active FROM inserted) = 1
BEGIN

Update T
T.active = 0
from [tablename] T inner join inserted I
on I.ID = T.ID
where ( T.aletrnate <> I.aletrnate  and T.version <> I.Version)

END


CREATE TRIGGER TrgINS ON [tablename]
FOR Update
AS

IF (SELECT active FROM inserted) = 1
BEGIN

Update T
T.active = 0
from [tablename] T inner join inserted I
on I.ID = T.ID
where ( T.aletrnate <> I.aletrnate  and T.version <> I.Version)

END
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pucko73Author Commented:
can't compile this trigger.
0
pucko73Author Commented:
I guess that there must be:

where ( T.aletrnate <> I.aletrnate  OR T.version <> I.Version)

instead?
0
pucko73Author Commented:
and I get this;

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
0
Bhavesh ShahLead AnalysistCommented:
hi,

check out this

CREATE TRIGGER TrgDel ON [tablename]
FOR DELETE
AS

IF (SELECT active FROM deleted) = 1
BEGIN

Update a
SET active =1 
from [table] a, (select T.IE, min(T.alternate)alternate, max(T.version)version
				 from
					[table] T INNER JOIN Deleted D on T.IE = D.IE) B
WHERE A.alternate = B.alternate
AND A.version = B.version
AND A.IE = B.IE

END

Open in new window

-------------------------------------
CREATE TRIGGER TrgINS ON [tablename]
FOR Insert
AS


Update T
SET T.active = 0 
from [tablename] T inner join inserted I
on I.ID = T.ID

Update T
SET T.active = 1
from [tablename] T inner join inserted I
on I.ID = T.ID
and I.alternate = I.alternate
AND I.version = I.version

Open in new window

0
Pratima PharandeCommented:
in which trigger you are getting this error Delete , insert or update ?
0
Pratima PharandeCommented:
CREATE TRIGGER TrgDel ON [tablename]
FOR DELETE
AS

IF (SELECT active FROM deleted) = 1
BEGIN

Update [tablename]
set active =1
where Alternate  = ( select min(alternate) from [tablename] T
 inner join deleted D on T.ID = D.ID )
and version = (select max(version) from [tablename] T
 inner join deleted D on T.ID = D.ID)

END
0
pucko73Author Commented:
I got that on the update trigger.
0
Pratima PharandeCommented:
is delete trigger is working fine ?
0
Pratima PharandeCommented:
CREATE TRIGGER TrgINS ON [tablename]
FOR Update
AS

IF (SELECT active FROM inserted) = 1
BEGIN

Update T
set T.active = 0
from [tablename] T inner join inserted I
on I.ID = T.ID

Update T
set T.active = 1
from [tablename] T inner join inserted I
on I.ID = T.ID
where  T.aletrnate = I.aletrnate  and T.version = I.Version)

END
0
pucko73Author Commented:
That won't work.

What will happen if I update a row  and try to set it to active=0 ?
0
pucko73Author Commented:
Sorry last comment was for brichoft
0
pucko73Author Commented:
but the IF (SELECT active FROM inserted) = 1
will cause the:


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
0
pucko73Author Commented:
However. this will work I think_

CREATE TRIGGER TrgINS ON [tablename]
FOR Insert
AS


Update T
SET T.active = 0
from [tablename] T inner join inserted I
on I.ID = T.ID

Update T
SET T.active = 1
from [tablename] T inner join inserted I
on I.ID = T.ID
and T.alternate = I.alternate
AND T.version = I.version
AND I.active=1
0
pucko73Author Commented:
the IF (SELECT active FROM inserted) = 1 will casue the same error in the delete trigger...
0
Bhavesh ShahLead AnalysistCommented:
i miss that

CREATE TRIGGER TrgUPD ON [tablename]
FOR Update
AS


IF (SELECT active FROM Inserted) = 0
BEGIN

Update a
SET active =1 
from [table] a, (select T.IE, min(T.alternate)alternate, max(T.version)version
				 from
					[table] T INNER JOIN Inserted D on T.IE = D.IE) B
WHERE A.alternate = B.alternate
AND A.version = B.version
AND A.IE = B.IE

END

Open in new window

0
Bhavesh ShahLead AnalysistCommented:
check out all triggers
0
Pratima PharandeCommented:
CREATE TRIGGER TrgDel ON [tablename]
FOR DELETE
AS

IF (SELECT active FROM deleted where active =1 ) = 1
BEGIN

Update [tablename]
set active =1
where Alternate  = ( select min(alternate) from [tablename] T
 inner join deleted D on T.ID = D.ID )
and version = (select max(version) from [tablename] T
 inner join deleted D on T.ID = D.ID)

END
0
Pratima PharandeCommented:
CREATE TRIGGER TrgDel ON [tablename]
FOR DELETE
AS

IF exists (SELECT active FROM deleted where active = 1)
BEGIN

Update [tablename]
set active =1
where Alternate  = ( select min(alternate) from [tablename] T
 inner join deleted D on T.ID = D.ID )
and version = (select max(version) from [tablename] T
 inner join deleted D on T.ID = D.ID)

END
0
Scott PletcherSenior DBACommented:
Please try the triggers below.  SQL Server triggers must be coded extremely carefully because mltiple rows being INSERTed or UPDATEd by the same statement will all be passed to the same trigger invocation:



CREATE TRIGGER tablename_trg_INS_UPD
ON dbo.[tablename]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
--if a new active row is INSERTed or UPDATEd, change all other rows with the same ID to inactive.
UPDATE t
SET active = 0
FROM dbo.[tablename] t
INNER JOIN (
    -- the MAX() and MIN() are just in case more than one row with "active = 1" is INSERTed/UPDATEd at the same time
    SELECT ID, MAX(version) AS version, MIN(alternate) AS alternate
    FROM inserted
    WHERE active = 1
    GROUP BY ID
) AS i ON
    i.ID = t.ID AND
    -- set all rows *except* the new active one to inactive
    NOT (i.version = t.version AND i.alternate = t.alternate)

--if an existing ID has all rows set to inactive, determine the new active ID.
UPDATE t
SET active = 1
FROM dbo.[tablename] t
INNER JOIN (
    --determine the row to be made active if a just-modified ID does not now contain any active row;
    --note that it is possible for a row that was set by the caller to inactive to be made active again by this code if
    --that row ends up having the highest version and lowest alternate values.
    SELECT t2.ID, MAX(t2.version) AS version, MIN(t2.alternate) AS alternate
    FROM dbo.tablename t2
    INNER JOIN inserted i2 ON
        i2.ID = t2.id
    GROUP BY t2.ID
    HAVING MAX(t2.active) = 0
) AS new_active ON
    new_active.ID = t.ID AND
    new_active.version = t.version AND
    new_active.alternate = t.alternate

GO

CREATE TRIGGER tablename_trg_DEL
ON dbo.[tablename]
FOR DELETE
AS
SET NOCOUNT ON
UPDATE t
SET active = 1
FROM dbo.[tablename] t
INNER JOIN (
    SELECT ID
    FROM deleted
    WHERE active = 1
) AS d ON
    d.ID = t.ID    
INNER JOIN (
    SELECT ID, MAX(version) AS version, MIN(alternate) AS alternate
    FROM dbo.tablename
    GROUP BY ID
) AS t_active ON
    t_active.ID = t.ID AND
    t_active.version = t.version AND
    t_active.alternate = t.alternate
   
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Thanks!

But NOTE:

The code above assumes "alternate" values always begin at 1 (the minimum value possible) for each and every new version.  If that is not true, the code needs adjusted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.