I have a table, SecurityMasterUnderlying, 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
SecurityMasterUnderlying.
* 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 [SecurityMasterUnderlying] (
[Symbol] VARCHAR(15) NOT NULL,
[Identity] VARCHAR(15) NOT NULL,
[UnderlyingSymbol] VARCHAR(15) NOT NULL,
[UnderlyingIdentity] VARCHAR(15) NOT NULL,
[Shares] INT NOT NULL
) ON [PRIMARY]
GO

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

ALTER TABLE [dbo].[SecurityMasterUnderlying]
ADD
CONSTRAINT [FK_SMUnderlying_SM] FOREIGN KEY (
[Symbol], [Identity]
)
REFERENCES [dbo].[SecurityMaster] (
[Symbol], [Identity]
) ON DELETE CASCADE


ALTER TABLE [dbo].[SecurityMasterUnderlying]
ADD
CONSTRAINT [FK_SMUnderlying2_SM] FOREIGN KEY (
[UnderlyingSymbol], [UnderlyingIdentity]
)
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 SecurityMasterUnderlying VALUES ('DIAXL', '7635TRS',
'DIA', '654FE32', 100)