Help with delete trigger

[Jason]

I have a table, SecurityMasterU nderlying, that has a 2 foreign key
constraints to the same table (SecurityMaster ). The first one is a
one-to-one relationship. The second being a many-to-one.

* All securities (equities, options, futures) will have ONE row in
SecurityMaster.
* All derivatives (options) will also have ONE row in
SecurityMasterU nderlying.
* All derivatives will have an underlying security (think of it as a
parent) in SecurityMaster. The underlying security CAN have multiple
childs.

I cannot use ON DELETE CASCADE on both FK definitions since that
creates a circular reference so I need to use a trigger on one of
them.

I am having trouble writing the trigger that will delete the
derivative (from SecurityMaster) if the underlying security is
deleted. (With the sample data -- DIAXL should be deleted if DIA is)
CREATE TABLE [SecurityMaster] (
[Symbol] VARCHAR(15) NOT NULL,
[Identity] VARCHAR(15) NOT NULL,
[Name] VARCHAR(50) NULL
) ON [PRIMARY]

CREATE TABLE [SecurityMasterU nderlying] (
[Symbol] VARCHAR(15) NOT NULL,
[Identity] VARCHAR(15) NOT NULL,
[UnderlyingSymbo l] VARCHAR(15) NOT NULL,
[UnderlyingIdent ity] VARCHAR(15) NOT NULL,
[Shares] INT NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SecurityMaster]
ADD
CONSTRAINT [PK_SecurityMast er] PRIMARY KEY NONCLUSTERED (
[Symbol], [Identity]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SecurityMasterU nderlying]
ADD
CONSTRAINT [FK_SMUnderlying _SM] FOREIGN KEY (
[Symbol], [Identity]
)
REFERENCES [dbo].[SecurityMaster] (
[Symbol], [Identity]
) ON DELETE CASCADE
ALTER TABLE [dbo].[SecurityMasterU nderlying]
ADD
CONSTRAINT [FK_SMUnderlying 2_SM] FOREIGN KEY (
[UnderlyingSymbo l], [UnderlyingIdent ity]
)
REFERENCES [dbo].[SecurityMaster] (
[Symbol], [Identity]
)
GO

INSERT INTO SecurityMaster VALUES ('MSFT', '00764G53A', 'Microsoft')
INSERT INTO SecurityMaster VALUES ('DIA', '654FE32', 'Diamond')
INSERT INTO SecurityMaster VALUES ('DIAXL', '7635TRS', 'DIA Option')

INSERT INTO SecurityMasterU nderlying VALUES ('DIAXL', '7635TRS',
'DIA', '654FE32', 100)