HI all,

I have an insert trigger that inserts additional rows based on the row that
the user inserts
I also have a bridging table that gives me a  m:m relationship with another
table.
After the user insert rows into the policysection_division table, the
trigger then inserts multiple rows(depending on various other factors) into
the policysection_division table, they then choose a cover to match
I want to add the related row into the bridging table for only those rows
that were recently added to the policysection_division table (via user and
trigger)
So that all the recently added policysection_division rows are directly
related to the recently choosen cover ro via the rows inserted into the
bridging table
Part of my trigger

declare @divkey int, @depth int, @pskey int , @bordcode int, @policyperiod
int
set @depth = 5
select @divkey = i.divkey, @pskey =  i.PSKey,  @bordcode = i.bordcode from
inserted i
join policysection p on p.pskey=i.pskey
INSERT INTO policysection_division  SELECT @pskey, divkey,@bordcode,
@policyperiod FROM Division WHERE lineage LIKE ('%'+ltrim(str(@divkey))+'%')

Thanks
Robert

Please post DDL and sample data.


ML

---
http://milambda.blogspot.com/

HI,

Here it is

CREATE TABLE [dbo].[Covertype] (
  [CTKey] int IDENTITY(0, 1) NOT NULL,
  [ClassofCover] varchar(40) COLLATE Latin1_General_CI_AS NOT NULL,
  [PSD_Key] int,
  [Covercode] varchar(8) COLLATE Latin1_General_CI_AS,
  [Description] varchar(50) COLLATE Latin1_General_CI_AS,
  [free] bit CONSTRAINT [DF_Covertype_free] DEFAULT 0 NOT NULL
)

CREATE TABLE [dbo].[PolicySection_Division] (
  [PSD_Key] int IDENTITY(0, 1) NOT NULL,
  [PSKey] int NOT NULL,
  [divkey] int NOT NULL,
  [PeriodKey] int,
  [Bordcode] char(3) COLLATE Latin1_General_CI_AS
)

CREATE TABLE [dbo].[PSD_Covertype] (
  [PSD_Key] int NOT NULL,
  [psdctkey] int IDENTITY(1, 1) NOT NULL,
  [CTKey] int NOT NULL
)

INSERT INTO [dbo].[Covertype] ([CTKey], [ClassofCover], [PSD_Key],
[Covercode], [Description], [free])
VALUES  (1, 'FG', 2, '12223201', 'FG for Tiger', 0)
GO

INSERT INTO [dbo].[Covertype] ([CTKey], [ClassofCover], [PSD_Key],
[Covercode], [Description], [free])
VALUES  (2, 'Variabe', 4, '13223201', 'Variable for Tiger', 1)
GO

INSERT INTO [dbo].[Covertype] ([CTKey], [ClassofCover], [PSD_Key],
[Covercode], [Description], [free])
VALUES  (7, 'motor tes', NULL, '12223201', 'test', 0)
GO

INSERT INTO [dbo].[PSD_Covertype] ([PSD_Key], [psdctkey], [CTKey])
VALUES  (179, 86, 1)
GO

INSERT INTO [dbo].[PolicySection_Division] ([PSD_Key], [PSKey], [divkey],
[PeriodKey], [Bordcode])
VALUES  (173, 89, 1316, 130, '506')
GO

INSERT INTO [dbo].[PolicySection_Division] ([PSD_Key], [PSKey], [divkey],
[PeriodKey], [Bordcode])
VALUES  (174, 89, 1337, 130, '506')
GO

INSERT INTO [dbo].[PolicySection_Division] ([PSD_Key], [PSKey], [divkey],
[PeriodKey], [Bordcode])
VALUES  (175, 89, 1832, 130, '506')
GO

INSERT INTO [dbo].[PolicySection_Division] ([PSD_Key], [PSKey], [divkey],
[PeriodKey], [Bordcode])
VALUES  (176, 89, 2043, 130, '506')
GO

INSERT INTO [dbo].[PolicySection_Division] ([PSD_Key], [PSKey], [divkey],
[PeriodKey], [Bordcode])
VALUES  (177, 89, 2045, 130, '506')
GO

INSERT INTO [dbo].[PolicySection_Division] ([PSD_Key], [PSKey], [divkey],
[PeriodKey], [Bordcode])
VALUES  (178, 89, 2285, 130, '506')
GO

INSERT INTO [dbo].[PolicySection_Division] ([PSD_Key], [PSKey], [divkey],
[PeriodKey], [Bordcode])
VALUES  (179, 89, 2288, 130, '506')
GO

ALTER TABLE [dbo].[PSD_Covertype]
ADD CONSTRAINT [FK__PSD_Cover__PSD_K__49EEDF40] FOREIGN KEY ([PSD_Key])
  REFERENCES [dbo].[PolicySection_Division] ([PSD_Key])
  ON UPDATE NO ACTION
  ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PSD_Covertype]
ADD CONSTRAINT [FK__PSD_Cover__CTKey__4AE30379] FOREIGN KEY ([CTKey])
  REFERENCES [dbo].[Covertype] ([CTKey])
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

Thanks
RObert

Show quoteHide quote

What's the PSD_Key column in the Covertype table for? Are the Covertype and
PoliciSection_Division tables directly related? If so, why is there a third
intermediate table?


ML

---
http://milambda.blogspot.com/

ML wrote:
Show quoteHide quote

help for query
Proper Index On a LIKE and GROUP BY query
Complex data extract from excel
How to calculate future working date
Calculate fee using tiered fee structure