Jason (Ja*******@hotmail.com) writes:
I have a table that matches up Securities and Exchanges. Individual
securities can belong on multiple exchanges. One of the columns, named
PrimaryExchangeFlag, indicates if a particular exchange is the primary
exchange for that symbol. Each symbol can only have one primary
exchange.
I am trying to write a insert/update/delete trigger that enforces this
rule. The rules I have thought of are as follows:
Insert If new row has flag set, turn off flag for other rows for
that symbol. Otherwise, do nothing.
Update If updated row has flag set, turn off flag for other rows
for that symbol. Otherwise, set flag on first (MAX or MIN or TOP 1
???) row for that symbol.
Delete If deleted row had flag set, set flag on first row for that
symbol. Otherwise, do nothing.
My basic problem is how to do this when the trigger gets thrown for
multiple rows. (Since SQL does not throw individual triggers for each
row.)
The repro below includes a trigger that seems to fulfil the requirements.
To be able to determine "first row", I added an exch_no column. I also
added PRIMARY KEY and UNIQUE constraints to set the frames for what I'm
working with.
There is one potential problem: if you update PrimaryFlag for the
row with the lowest exch_no to 0 and if it was 1 before, the trigger
will flip it back to 1. This situation was not covered in your requirements.
The UPDATE statement in the trigger includes an EXISTS clause within
comments. As far as I can see, this clause is not needed from a functional
point of view. For performance, it could have. Then again, accessing
the inserted/deleted tables can be expensive, why it is often a good
idea to read them into a temp table or table variable.
Finally, if you expect one-row operations to be common, it may be a good
idea to say IF @@rowcount = 1 first in the trigger and have a special
code path for this case, as you may avoid performance problems in this
case.
Here is a script:
CREATE TABLE TestTable (
[Symbol] VARCHAR(15),
[Exchange] VARCHAR(5),
exch_no int,
[PrimaryFlag] BIT,
CONSTRAINT pk_exch PRIMARY KEY (Symbol, Exchange),
CONSTRAINT u_exch UNIQUE (Symbol, exch_no)
)
go
CREATE TRIGGER testtable_tri on TestTable FOR
INSERT, UPDATE, DELETE AS
UPDATE TestTable
SET PrimaryFlag = 0
FROM TestTable t
WHERE EXISTS (SELECT *
FROM inserted i
WHERE t.Symbol = i.Symbol
AND t.Exchange <> i.Exchange
AND i.PrimaryFlag = 1)
UPDATE TestTable
SET PrimaryFlag = 1
FROM TestTable t
JOIN (SELECT t.Symbol, exch_no = MIN(t.exch_no)
FROM TestTable t
WHERE t.PrimaryFlag = 0
AND NOT EXISTS (SELECT *
FROM TestTable t2
WHERE t2.Symbol = t.Symbol
AND t2.PrimaryFlag = 1)
GROUP BY t.Symbol) AS t3
ON t.Symbol = t3.Symbol
AND t.exch_no = t3.exch_no
/*WHERE EXISTS (SELECT *
FROM deleted d
WHERE d.Symbol = t.Symbol)*/
GO
INSERT INTO TestTable VALUES ('MSFT', 'XNAS', 1, 1)
-- first row - so flagshould be set
INSERT INTO TestTable VALUES ('QQQ', 'XNYS', 1, 1)
-- first row - so flag should be set
INSERT INTO TestTable VALUES ('QQQ', 'XASE', 2, 1)
-- second row - so flag should be set, but previous row should be unset
INSERT INTO TestTable VALUES ('MSFT', 'XASE', 2, 0)
-- second row - should be unset for this, previous row not changed
select * from TestTable ORDER BY Symbol, exch_no
UPDATE TestTable SET [PrimaryFlag] =0
WHERE [Symbol] = 'QQQ' AND [Exchange] = 'XNYS'
-- set flag, unset first row
select * from TestTable ORDER BY Symbol, exch_no
DELETE FROM TestTable WHERE [Symbol] = 'QQQ' AND [Exchange] = 'XASE'
-- first row should be set
DELETE FROM TestTable WHERE [Symbol] = 'MSFT' AND [Exchange] = 'XASE'
-- no change to flags
GO
SELECT * FROM [TestTable] ORDER BY Symbol, exch_no
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
