|
|
Posted by coosa on 12/11/05 19:56
USE MASTER
GO
IF DB_ID('TEST_DB') IS NOT NULL
BEGIN
DROP DATABASE TEST_DB
END
GO
CREATE DATABASE TEST_DB
GO
USE TEST_DB
GO
BEGIN TRAN PROCESS_TABLES
GO
CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINT
UC__CATEGORY__CATEGORY_NAME UNIQUE,
PARENT_CATEGORY_ID INTEGER,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
GO
CREATE UNIQUE INDEX IDX__CATEGORY__CATEGORY_NAME ON CATEGORY
(CATEGORY_NAME ASC)
GO
CREATE UNIQUE INDEX IDX__CATEGORY__CATEGORY_ID ON CATEGORY
(CATEGORY_ID)
GO
CREATE TRIGGER AI_CATEGORY
ON CATEGORY
AFTER INSERT AS
UPDATE C
SET DEPTH = coalesce(p.DEPTH, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGORY_ID = P.CATEGORY_ID
GO
CREATE TRIGGER AU_CATEGORY
ON CATEGORY
AFTER UPDATE AS
DECLARE @LVL INT
DECLARE @ROWC INT
DECLARE @AFFECTED TABLE (
CATEGORY_ID INT NOT NULL PRIMARY KEY,
LVL INT NOT NULL
)
SELECT @LVL = 1
INSERT @AFFECTED(CATEGORY_ID, LVL)
SELECT CATEGORY_ID,
@LVL
FROM INSERTED
SELECT @ROWC = @@ROWCOUNT
WHILE @ROWC <> 0
BEGIN
UPDATE C
SET DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
LEFT JOIN CATEGORY P
ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
WHERE EXISTS (
SELECT *
FROM @AFFECTED A
WHERE C.CATEGORY_ID = A.CATEGORY_ID
AND A.LVL = @LVL
)
SELECT @LVL = @LVL + 1
INSERT @AFFECTED (CATEGORY_ID, LVL)
SELECT C.CATEGORY_ID, @LVL
FROM CATEGORY C
WHERE EXISTS (
SELECT *
FROM @AFFECTED A
WHERE A.CATEGORY_ID = C.PARENT_CATEGORY_ID
)
AND NOT EXISTS (
SELECT *
FROM @AFFECTED A
WHERE A.CATEGORY_ID = C.CATEGORY_ID
)
SELECT @ROWC = @@ROWCOUNT
END
GO
INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('PC HARDWARE')
GO
INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('MOBILES')
GO
INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('CAMERAS')
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('NETWORKS', 1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('AUDIO', 1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('VIDEO', 1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('HARD
DRIVES', 1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('MEMORY', 1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('MOTHERBOARDS', 1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('CPU',
1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('SWITCHES', 4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('HUBS',
4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('MODEMS', 4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('ROUTERS', 4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('BRIDGES', 4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('EXTERNAL MODEMS', 13)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('INTERNAL MODEMS', 13)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('DSL
MODEMS', 16)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('ISDN
MODEMS', 16)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('GRAPHIC CARDS', 6)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AGP
GRAPHIC CARDS', 20)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('PCI
GRAPHIC CARDS', 20)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AUDIO
CARDS', 5)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('PCI
AUDIO CARDS', 23)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('ISA
AUDIO CARDS', 23)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('SPEAKER SYSTEMS', 5)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('2.0
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('2.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('4.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('5.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('6.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('7.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('SPEAKER DECODER', 26)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('HEAD
PHONES', 5)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('EAR
PHONES', 5)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AUDIO
ACCESSORIES', 5)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('EXTERNAL DEVICES', 5)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('DUPLICATION', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('ENCLOSURE', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('INTERNAL SCSI', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('SERIAL
ATA', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('FIREWIRE', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('EXTERNAL USB', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('NETWORK ATTACHED STORAG', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('INTERNAL IDE', 7)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('TV
CARDS', 6)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AGP
016MB', 21)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AGP
032MB', 21)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AGP
064MB', 21)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AGP
128MB', 21)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AGP
256MB', 21)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('AGP
512MB', 21)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('CONTROLLER',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('SCANNER',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('CASING',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('OPTICAL DEVICES',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('DISPLAY',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('INPUT
DEVICES',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('COOLER',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('POWER',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('PRINTER',1)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('EIDE-ATA',53)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('SCSI-RAID',53)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('SCSI',53)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('PCMCIA
INTERFACE',53)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('ISA
INTERFACE',53)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('PCI
INTERFACE',53)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('CD-R',56)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('CD-RW',56)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('DVD-R',56)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('DVD-RW',56)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('MONITOR',57)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('PLASMA',57)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('PROJECTOR',57)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('TOUCH
SCREEN',57)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('DISPLAY ACCESSORIES',57)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('TABLETS & HANDWRITING',58)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('JOYSTICK & GAME PAD',58)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('KEYBOARD & KEYPAD',58)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('MICE',58)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('MOUSE
PAD & WRIST REST',58)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('MICROPHONE',58)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('THERMAL GEL',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('LIQUID
COOLING',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('FAN
CONTROLLER',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('HARD
DRIVE COOLER',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('CPU
FAN',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('FAN
FUNNEL',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('SHIM',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('CHIPSET FAN',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('CASE
FAN',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('MEMORY
COOLER',59)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('POWER
SUPPLY',60)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('POWER
PROTECTION',60)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('POWER
ACCESSORIES',60)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('SINGLE
PRINTER',61)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('MFC -
ALL IN ONE',61)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('PRINTER ACCESSORIES',61)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('NETWORK ACCESSORIES',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('VOIP',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('KVM',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('GATEWAY',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('FIREWALL',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('NETWORK CABLE',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('STARTER KIT',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('ATTACHED STORAGE',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES
('ETHERNET ADAPTER',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('PATCH
PANEL',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME,PARENT_CATEGORY_ID) VALUES ('MEDIA
CONVERTER',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('NOTEBOOK')
GO
COMMIT TRAN PROCESS_TABLES
GO
[Back to original message]
|