trigger know how - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

trigger know how

Author

Topic

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 09:37:05

I am not sure which sub-forum to place this plea for help, so I put it here.
I have a trigger on table A which has a one to many relationship with table B (via foreign key). I have been trying desperately for days to come up with code that will allow the trigger on table A to update multiple rows on table B. No luck.
I have a need to check for a value in a datetime field in table A.
If it is populated then copy that datetime value into a datetime field in multiple rows on table B.

My trigger seems to work just fine when only one related row exists in table B, but not when there is more than one related row in table B. Can someone give an example of how to do this? ...please?

Kevin W. Plath (c) J
Sr Programmer | Database Administrator | Contractor
DLCI 6.6 | 2 ASG Petawawa | the Adga Groupe
Défense nationale | National Defence
Petawawa, Canada K8H 2X3
Kevin.Plath@forces.gc.ca
Telephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969
Gouvernement du Canada | Government of Canada

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 09:38:50

Can you please post the code that you have written so far for your trigger.

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 09:53:26

In this first example I kept it simple then in the second I tried to join with the temporary / virtual table inserted to attempt to update multiple rows - neither updated multiple records.

Example 1:
USE [ACSDD]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [_SMDBA_].[AUTO_RETURN] ON [_SMDBA_].[LOANCARD_]
FOR UPDATE
AS
Declare @mydate as datetime
Set @mydate = (select [RETURNDATE] from inserted)
If (Select [RETURNDATE] from inserted) is not NULL
BEGIN
Update _SMDBA_.LC_ITEMS_
Set [CSBR_RDATE] = @mydate
where [LCSEQ] = (Select [SEQUENCE] from deleted)
END

Example 2:
USE [ACSDD]

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[_SMDBA_].[LOANCARD_]'))
DROP TRIGGER [_SMDBA_].[LOANCARD_]
GO

/****** Object: Trigger [_SMDBA_].[AUTO_RETURN] Script Date: 06/09/2010 15:33:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET NoCount ON
Go
ALTER TRIGGER [_SMDBA_].[AUTO_RETURN] ON [_SMDBA_].[LOANCARD_]
AFTER UPDATE
AS

--Check for change in return date value
If (Select [RETURNDATE] from Inserted) <> (Select [RETURNDATE] from Deleted)
Begin
If (Select [RETURNDATE] from inserted) is not NULL
BEGIN
update _SMDBA_.LC_ITEMS_
set _SMDBA_.LC_ITEMS_.[CSBR_RDATE] = (Select [RETURNDATE] from Inserted)
From (Select _SMDBA_.LC_ITEMS_.[SEQUENCE] as ISEQ
FROM inserted i inner join _SMDBA_.LC_ITEMS_
on _SMDBA_.LC_ITEMS_.[LCSEQ] = i.[SEQUENCE]) TJ
where _SMDBA_.LC_ITEMS_.[SEQUENCE] = TJ.[ISEQ]

END
End
Set NoCount OFF;

Kevin W. Plath (c) J
Sr Programmer | Database Administrator | Contractor
DLCI 6.6 | 2 ASG Petawawa | the Adga Groupe
Défense nationale | National Defence
Petawawa, Canada K8H 2X3
Kevin.Plath@forces.gc.ca
Telephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969
Gouvernement du Canada | Government of Canada

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 10:15:06

Try this

CREATE TRIGGER [_SMDBA_].[AUTO_RETURN]
ON [_SMDBA_].[LOANCARD_] 
FOR UPDATE
AS
BEGIN
	IF UPDATE(RETURNDATE)
	BEGIN
		UPDATE A
		SET A.[CSBR_RDATE] = B.RETURNDATE
		FROM  _SMDBA_.LC_ITEMS_ A 
		INNER JOIN Inserted B ON A.[LCSEQ] = B.[SEQUENCE]
		WHERE B.RETURNDATE IS NOT NULL
	END
END

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 10:50:35

It looked impressive but did not work.
Trigger fires successfully for single row in LC_ITEMS_(I already acheived this much).
However when there are multiple rows to be updated, the multiple rows do not get updated and the source table has the date field cleared in the front end application, when saved.
Note: I have disabled all other code (i.e. bus rules managed by front end) that could possibly contribute to the table/trigger problem.

Any other ideas?

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 10:54:16

Although I am pretty good at creating them, I don't want to use a cursor in a trigger. I'd prefer to use some flavour of rowset logic.
I just don't know how to make the trigger update multiple records.
It doesn't sound like it should be all that hard to do.

Kevin W. Plath (c) J
Sr Programmer | Database Administrator | Contractor
DLCI 6.6 | 2 ASG Petawawa | the Adga Groupe
Défense nationale | National Defence
Petawawa, Canada K8H 2X3
Kevin.Plath@forces.gc.ca
Telephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969
Gouvernement du Canada | Government of Canada

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 11:00:48

quote:
Originally posted by kezmaster

It looked impressive but did not work.
Trigger fires successfully for single row in LC_ITEMS_(I already acheived this much).
However when there are multiple rows to be updated, the multiple rows do not get updated and the source table has the date field cleared in the front end application, when saved.
Note: I have disabled all other code (i.e. bus rules managed by front end) that could possibly contribute to the table/trigger problem.

Any other ideas?

Can you please post table structure and sample data from both tables.

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 11:40:44

quote:
Originally posted by vijayisonly

Can you please post table structure and sample data from both tables.

what would you like? ...screen captures from studio manager?
Is there an email address I could send these attachments too?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 11:46:53

Something like this
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 14:41:55

quote:
Originally posted by vijayisonly

Something like this
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Here is the first table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [_SMDBA_].[LOANCARD_](
[SEQUENCE] [int] NOT NULL,
[LASTMODIFIED] [datetime] NOT NULL CONSTRAINT [DFLOANCARD_LASTMODIFIED] DEFAULT (getdate()),
[LASTUSER] [varchar](30) NOT NULL CONSTRAINT [DFLOANCARD_LASTUSER] DEFAULT (suser_sname()),
[_GROUP_] [int] NULL,
[_INACTIVE_:] [smallint] NOT NULL CONSTRAINT [DFLOANCARD__INACTIVE_:] DEFAULT (0),
[CLIENTSEQ] [int] NULL,
[UNITSEQ] [int] NULL,
[STAFFSEQ] [int] NULL,
[SDISEQ] [int] NULL,
[WOSEQ] [int] NULL,
[BEGINDATE] [datetime] NULL CONSTRAINT [DFLOANCARD_BEGINDATE] DEFAULT (getdate()),
[RETURNDATE] [datetime] NULL,
[NOTIFICATION1] [smallint] NULL CONSTRAINT [DFLOANCARD_NOTIFICATION1] DEFAULT (0),
[NOTIFICATION2] [smallint] NULL CONSTRAINT [DFLOANCARD_NOTIFICATION2] DEFAULT (0),
[TERM] [nvarchar](16) NULL CONSTRAINT [DFLOANCARD_TERM] DEFAULT ('No Term Selected'),
[NOTES] [text] NULL,
CONSTRAINT [PKLOANCARD_] PRIMARY KEY NONCLUSTERED
(
[SEQUENCE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [_SMDBA_].[LOANCARD_] WITH NOCHECK ADD CONSTRAINT [CHKLOANCARD_] CHECK (([SEQUENCE] <> 0 and ([_INACTIVE_:] = 1 or [_INACTIVE_:] = 0) and ([NOTIFICATION1] = 1 or [NOTIFICATION1] = 0) and ([NOTIFICATION2] = 1 or [NOTIFICATION2] = 0) and ([TERM] = 'No Term Selected' or ([TERM] = '1 year' or ([TERM] = '6 months' or ([TERM] = '3 months' or ([TERM] = '2 months' or ([TERM] = '1 month' or ([TERM] = '3 weeks' or ([TERM] = '2 weeks' or ([TERM] = '1 week' or ([TERM] = '3 days' or ([TERM] = '2 days' or ([TERM] = '1 day' or [TERM] = 'Same Day'))))))))))))))
GO
ALTER TABLE [_SMDBA_].[LOANCARD_] CHECK CONSTRAINT [CHKLOANCARD_]

Sample Data:
1092 10/3/2005 12:19:10 PM MAGIC 1 0 21277 1744 1206 NULL NULL 10/3/2005 11:58:16 AM NULL 0 0 No Term Selected NULL
1415 4/3/2006 11:51:57 AM GENOEMG 1490 0 11136 1060 2623 1059929 697487 3/14/2006 8:18:26 AM 4/3/2006 11:51:53 AM 0 0 2 weeks 170 - 639-2180
183 - 639-1710
2609 1/31/2008 9:41:03 AM CHEVALIERCJ 1256 1 64065 1135 2150 NULL NULL 2/2/2007 11:02:52 AM 1/31/2008 9:40:59 AM 0 0 No Term Selected NULL
2055 8/8/2007 11:02:19 AM LOUGHKM 1256 0 71474 NULL 2664 NULL NULL 9/13/2006 1:14:46 PM NULL 0 0 No Term Selected NULL
1447 4/12/2006 10:56:17 AM BRAKENBURYAE 1490 1 51870 1151 2783 NULL 701568 3/22/2006 12:05:06 PM 4/12/2006 10:56:09 AM 0 0 No Term Selected NULL
2249 11/16/2006 9:51:17 AM MACKINPK 1352 1 13606 1532 1256 NULL NULL 10/20/2006 5:15:15 PM 11/16/2006 9:51:00 AM 0 0 No Term Selected REPLACE HQ ON RETURN
1315 4/5/2006 1:01:15 PM GENOEMG 1490 0 57014 1194 2623 1033602 684216 1/27/2006 9:22:00 AM 4/5/2006 1:01:09 PM 0 0 No Term Selected NULL
1925 8/22/2006 1:47:06 PM BAYESJH 1256 0 16070 1135 2461 1150017 739605 8/22/2006 1:46:02 PM NULL 0 0 No Term Selected NULL
2969 8/29/2007 3:07:37 PM GRIESEV 1201 0 53494 1455 3159 1290520 811061 5/24/2007 3:25:55 PM NULL 0 0 3 weeks References WO 810969 (Litepro)

Item 6RC'd as in Solution column of WO 811061 (cpl Noel)
Loan card destroyed
2363 11/22/2006 3:28:58 PM LETOURNEAUMS 1352 1 14494 1575 1256 1203087 763328 11/20/2006 4:20:50 PM 11/22/2006 3:28:03 PM 0 0 1 week Item return
2400 1/31/2008 9:40:49 AM CHEVALIERCJ 1256 1 64065 1135 2664 1209569 766428 11/29/2006 9:48:03 AM 12/10/2006 10:16:46 AM 0 0 No Term Selected Temp Isssue for Hockey Tour( Dec01-08)//JAB

Kevin W. Plath (c) J
Sr Programmer | Database Administrator | Contractor
DLCI 6.6 | 2 ASG Petawawa | the Adga Groupe
Défense nationale | National Defence
Petawawa, Canada K8H 2X3
Kevin.Plath@forces.gc.ca
Telephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969
Gouvernement du Canada | Government of Canada

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 15:08:15

quote:
Originally posted by vijayisonly

Something like this
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Here's the second table:

USE [ACSDD]
GO
/****** Object: Table [_SMDBA_].[LC_ITEMS_] Script Date: 06/10/2010 15:05:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [_SMDBA_].[LC_ITEMS_](
[SEQUENCE] [int] NOT NULL,
[LASTMODIFIED] [datetime] NOT NULL CONSTRAINT [DFLC_ITEMS_LASTMODIFIED] DEFAULT (getdate()),
[LASTUSER] [varchar](30) NOT NULL CONSTRAINT [DFLC_ITEMS_LASTUSER] DEFAULT (suser_sname()),
[_GROUP_] [int] NULL,
[_INACTIVE_:] [smallint] NOT NULL CONSTRAINT [DFLC_ITEMS__INACTIVE_:] DEFAULT (0),
[LCSEQ] [int] NULL,
[ITMSEQ] [int] NULL,
[ITMRETURN] [smallint] NULL CONSTRAINT [DFLC_ITEMS_ITMRETURN] DEFAULT (0),
[CSBR_RDATE] [datetime] NULL,
[LCI_IISEQ] [int] NULL,
[LCI_ASSETNO] [varchar](25) NULL,
[LCI_CATID] [varchar](25) NULL,
[LCI_IDESCR] [varchar](50) NULL,
[LCI_SERIAL] [varchar](25) NULL,
[ITMBEGINDATE] [datetime] NULL CONSTRAINT [DFLC_ITEMS_ITMBEGINDATE] DEFAULT (getdate())
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [_SMDBA_].[LC_ITEMS_] ADD [ASSOCNUM] [varchar](15) NULL
/****** Object: Index [PKLC_ITEMS_] Script Date: 06/10/2010 15:05:36 ******/
ALTER TABLE [_SMDBA_].[LC_ITEMS_] ADD CONSTRAINT [PKLC_ITEMS_] PRIMARY KEY NONCLUSTERED
(
[SEQUENCE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [_SMDBA_].[LC_ITEMS_] WITH CHECK ADD CONSTRAINT [CHKLC_ITEMS_] CHECK (([SEQUENCE]<>(0) AND ([_INACTIVE_:]=(1) OR [_INACTIVE_:]=(0)) AND ([ITMRETURN]=(1) OR [ITMRETURN]=(0))))
GO
ALTER TABLE [_SMDBA_].[LC_ITEMS_] CHECK CONSTRAINT [CHKLC_ITEMS_]

sample data:
1110 3/30/2006 11:49:00 AM PLATHKW 1256 0 1044 NULL 1 8/9/2005 9:13:56 AM 40539 1060000089 DELL-PC-GX150 Dell Optiplex GX150 DeskTop PC 2Z5M011 8/10/2005 9:12:44 AM NULL
1280 4/17/2006 4:16:48 PM FORCES\Gardner.SR 1338 0 1096 NULL 1 10/12/2005 12:19:07 PM 40623 1012006094 DELL-LAP-P4 1.8GHZ Dell Latitude D600 Lap Top P4 1.8 Ghz 8BN4071 10/11/2005 11:36:40 AM NULL
1542 1/20/2006 1:42:36 PM ARSENAULTBM 1223 0 1273 NULL 0 NULL 44065 1002001079 SAMSUNG660 Samsung Cell Phone 660 04115370988 1/20/2006 1:42:36 PM NULL
4228 1/26/2007 1:07:39 PM BARKLEYJA 1256 0 2537 NULL 1 1/24/2007 10:36:30 AM 51450 1060119603 LAP-PAN-CF28 Panasonic CF28 Touch Screen Laptop CF28STJGZDM-G 1/19/2007 9:48:31 AM (___)___-____
2726 4/26/2007 1:36:25 PM GRIESEV 1490 0 1796 NULL 1 4/26/2007 1:35:12 PM 47031 1020007616 CPQ-PC-1.8GHZ Compaq EVO 1.8 Ghz Desk Top PC 6Y26JYHZA1B5 7/24/2006 11:01:34 AM NULL
3069 8/25/2006 10:29:18 AM BAYESJH 1256 0 1914 NULL 0 NULL 48867 1060109156 DELL-PC-GX150 Dell Optiplex GX150 DeskTop PC 2P5M011 8/25/2006 10:29:18 AM NULL
3248 10/5/2006 8:59:13 AM GENOEMG 1490 0 1936 NULL 1 10/5/2006 8:59:08 AM 43826 1002000189 MOT-CELL-I30SX Motorola Cell Phone 00010181440300 9/6/2006 8:55:34 AM NULL
2237 10/19/2006 10:55:22 AM EIGLERPA 1490 0 1580 NULL 1 10/19/2006 10:55:14 AM 43945 1002001012 NOK-CELL-3120 Nokia Cell Phone 03703837437 5/9/2006 2:25:34 PM NULL
4045 6/18/2008 1:29:56 PM GRIESEV 1205 0 2418 NULL 1 6/18/2008 1:29:41 PM 48489 1020007692 MON-CRT-PHIL-109B4 Philips 19" CRT Monitor BZ000312222405 12/5/2006 10:17:58 AM NULL
1572 3/30/2006 12:05:22 PM PLATHKW 1490 0 1296 NULL 1 3/9/2006 2:52:40 PM 44068 1002000225 MOT-CELL-I30SX MOTOROLA CELL PHONE I30SX 000101860696300 1/25/2006 9:25:10 AM NULL
3558 2/26/2007 12:47:23 PM PYKER 1490 0 2158 NULL 1 2/26/2007 12:47:20 PM 49432 1020008439 MOT-CELL-A840 Motorola Cell Phone 354074000199197 10/4/2006 2:58:20 PM NULL
1958 6/12/2006 1:56:22 PM PYKER 1490 0 1471 NULL 1 6/12/2006 1:56:16 PM 44067 1002000224 MOT-CELL-I30SX MOTOROLA CELL PHONE I30SX 000101814444300 4/3/2006 7:53:03 AM NULL
2498 11/1/2006 8:40:14 AM NOBLEKN 1205 0 1690 NULL 1 6/27/2006 10:59:51 AM 47617 1020007572 MON-CRT-PHIL-109B50 Philips 19" CRT Monitor CX000407236350 6/28/2006 9:30:59 AM NULL
2282 10/2/2006 11:48:08 AM MACKINPK 1352 0 1591 NULL 1 10/2/2006 11:48:01 AM 38110 1012000491 IBM-DOC-STN IBM Port Replicator 02K8668/02K8667 M01340404 5/12/2006 5:47:28 PM NULL
4371 2/9/2007 1:09:50 PM MACKINPK 1352 0 2632 NULL 1 2/9/2007 1:09:40 PM 51271 1012015816 PC-DEL-GX620 3.6 Dell Optiplex GX620 P4 3.6Ghz Desktop PC 55JD8C1 2/7/2007 3:55:49 PM (___)___-____
1703 2/22/2006 8:22:14 AM GENOEMG 1490 0 1367 NULL 1 2/22/2006 8:21:57 AM 43426 1002000013 NOK-CELL-3586I Nokia 3586I Cellular Phone 038/10481396 2/16/2006 11:21:59 AM NULL
1884 4/17/2006 4:16:48 PM FORCES\Gardner.SR 1338 0 1429 NULL 1 3/23/2006 1:57:17 PM 42226 1012006036 DELL-PC-P4 3.2 GHZ GX280 Dell Opitplex GX280 P4 3.2 Ghz Desk top PC 1NS6071 3/17/2006 5:01:36 PM NULL
1885 4/17/2006 4:16:48 PM FORCES\Gardner.SR 1338 0 1429 NULL 1 3/23/2006 1:57:17 PM 42243 1012006699 DELL-PC-P4 3.2 GHZ GX280 Dell Opitplex GX280 P4 3.2 Ghz Desk top PC 9Y76071 3/17/2006 5:01:50 PM NULL
1886 4/17/2006 4:16:48 PM FORCES\Gardner.SR 1338 0 1429 NULL 1 3/23/2006 1:57:17 PM 42244 1012006700 DELL-PC-P4 3.2 GHZ GX280 Dell Opitplex GX280 P4 3.2 Ghz Desk top PC 8F46071 3/17/2006 5:02:07 PM NULL
1723 8/17/2006 8:01:42 AM GENOEMG 1490 0 1373 NULL 1 8/16/2006 8:01:36 AM 43507 1002000026 LG-CELL-TM250 LG CELL PHONE TM250 05306926540 2/21/2006 1:39:37 PM NULL
1726 3/30/2006 12:09:27 PM PLATHKW 1490 0 1374 NULL 1 3/8/2006 1:33:21 PM 43822 1002000185 MOT-CELL-I30SX MOTOROLA CELL PHONE I30SX 000101813566300 2/22/2006 8:43:11 AM NULL
1732 6/2/2006 2:00:32 PM MACKINPK 1338 0 1379 NULL 1 6/2/2006 2:00:23 PM 18528 1012004121 CPQ-LAP-P700 M700 Compaq Armada PIII P700 LapTop Model M700 3J13JLC4N423 2/22/2006 4:00:26 PM NULL
1740 4/17/2006 4:16:48 PM FORCES\Gardner.SR 1338 0 1387 NULL 1 3/14/2006 4:15:37 PM 43916 1012005714 HIT-PROJ-CP-S235WF Hitachi CP-S235WF Projector F4G002381 2/28/2006 4:09:51 PM NULL
1742 4/17/2006 4:16:48 PM FORCES\Gardner.SR 1338 0 1387 NULL 1 3/14/2006 4:15:37 PM 42127 1012005385 DELL-LAP-P4 1.8GHZ Dell Latitude D600 P4 1.8 Ghz Laptop 472TR71 2/28/2006 5:08:37 PM NULL
1743 9/8/2006 10:30:15 AM LETOURNEAUMS 1338 0 1388 NULL 1 9/8/2006 10:23:40 AM 42224 1012006034 DELL-PC-P4 3.2 GHZ GX280 Dell Opitplex GX280 P4 3.2 Ghz Desk top PC 2RS6071 2/28/2006 5:10:02 PM NULL

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 15:20:54

I'm seeing the following values for [SEQUENCE] from [LOANCARD_] table
1092,1415,2609 etc..I dont see a corresponding record in [LC_ITEMS_] when i look in [LCSEQ]. How exactly are these two tables related?

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 16:13:18

quote:
Originally posted by vijayisonly

I'm seeing the following values for [SEQUENCE] from [LOANCARD_] table
1092,1415,2609 etc..I dont see a corresponding record in [LC_ITEMS_] when i look in [LCSEQ]. How exactly are these two tables related?

OK I just sent you random excerpts I'll send you related data now. Here's the first table

1092 10/3/2005 12:19:10 PM MAGIC 1 0 21277 1744 1206 NULL NULL 10/3/2005 11:58:16 AM NULL 0 0 No Term Selected NULL
1099 10/12/2005 4:58:13 PM CHRISTIELI 1416 0 20127 1721 1206 NULL NULL 10/12/2005 4:58:01 PM NULL 0 0 1 month NULL
1081 9/20/2005 4:03:52 PM CHRISTIELI 1416 0 21653 1714 1206 NULL NULL 9/20/2005 4:03:10 PM NULL 0 0 2 months NULL
1033 6/10/2010 10:40:13 AM PLATHKW 1256 0 35057 1135 3874 NULL NULL 8/2/2005 10:11:16 AM 6/10/2010 10:40:11 AM 0 0 1 day NULL
1036 8/3/2005 1:26:06 PM ROBERTSDJ 1256 0 80610 1135 2676 NULL NULL 8/3/2005 1:26:06 PM NULL 0 0 No Term Selected NULL
1048 6/9/2010 4:36:13 PM PLATHKW 1256 0 74356 1135 2677 NULL NULL 8/10/2005 3:10:43 PM 6/9/2010 4:36:05 PM 0 0 1 year NULL
1050 8/23/2005 2:46:09 AM MAGIC 1256 0 83595 1135 2676 NULL NULL 8/11/2005 12:18:30 PM NULL 0 0 No Term Selected NULL
1054 8/23/2005 2:46:35 AM MAGIC 1256 0 83521 1980 2676 NULL NULL 8/15/2005 1:37:00 PM NULL 0 0 No Term Selected NULL
1057 11/13/2009 9:29:31 AM PLATHKW 1256 0 59471 1980 2677 NULL 657369 8/18/2005 2:07:22 PM NULL 0 0 6 months NULL
1068 11/9/2006 10:00:26 AM GENOEMG 1273 0 41575 1066 2738 NULL NULL 9/8/2005 3:41:12 PM 11/9/2006 10:00:21 AM 0 0 No Term Selected NULL
1078 9/15/2005 2:50:37 PM CHRISTIELI 1416 0 20120 1738 1206 966203 NULL 9/15/2005 2:46:00 PM NULL 0 0 2 months NULL
1079 9/16/2005 3:02:45 PM CHRISTIELI 1416 0 21511 1729 1206 964968 NULL 9/16/2005 2:19:38 PM NULL 0 0 No Term Selected NULL
1082 9/21/2005 1:12:37 PM CHRISTIELI 1416 0 21653 1714 1206 NULL NULL 9/21/2005 1:12:25 PM NULL 0 0 2 months NULL
1083 9/22/2005 10:02:56 AM CHRISTIELI 1416 0 40835 1729 1206 NULL NULL 9/22/2005 10:02:30 AM NULL 0 0 No Term Selected NULL
1085 9/26/2005 11:28:10 AM CHRISTIELI 1416 0 20120 1738 1206 NULL NULL 9/26/2005 11:27:57 AM NULL 0 0 2 months NULL

Kevin W. Plath (c) J
Sr Programmer | Database Administrator | Contractor
DLCI 6.6 | 2 ASG Petawawa | the Adga Groupe
Défense nationale | National Defence
Petawawa, Canada K8H 2X3
Kevin.Plath@forces.gc.ca
Telephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969
Gouvernement du Canada | Government of Canada

kezmaster
Starting Member

16 Posts

Posted - 2010-06-10 : 16:18:37

quote:
Originally posted by vijayisonly

I'm seeing the following values for [SEQUENCE] from [LOANCARD_] table
1092,1415,2609 etc..I dont see a corresponding record in [LC_ITEMS_] when i look in [LCSEQ]. How exactly are these two tables related?

Here is table 2...
1250 9/16/2005 2:35:54 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 38094 1011000631 NOK-CELL-3586I NOKIA 3586I Cellular Phone 03808248272 9/16/2005 2:35:54 PM NULL
1253 9/16/2005 2:38:10 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 42782 1011004004 NOK-CELL-3586I NOKIA 3586I Cellular Phone 07414811974 9/16/2005 2:38:09 PM NULL
1255 10/26/2005 9:04:16 AM CHRISTIELI 1416 0 1081 NULL 1 10/26/2005 9:04:13 AM 35438 1011000798 NOK-CELL-3586I NOKIA 3586I Cellular Phone 04312597774 9/20/2005 4:04:26 PM NULL
1256 10/26/2005 9:04:32 AM CHRISTIELI 1416 0 1081 NULL 1 10/26/2005 9:04:30 AM 42810 1011004006 NOK-CELL-3285V Nokia 3285V Cellular Phone 07407824666 9/20/2005 4:05:32 PM NULL
2228 6/10/2010 10:41:00 AM PLATHKW 1256 0 1033 NULL 0 NULL 45716 1060109471 DELL-PC-GX150 Dell Optiplex GX150 DeskTop PC 155M011 5/8/2006 10:31:57 AM NULL
1126 8/23/2005 2:50:12 AM MAGIC 1256 0 1050 NULL 0 NULL 42488 1060000331 HP I PAQ H2210 HP I Paq H2210 pocket PC TWC4330PGR 8/11/2005 12:54:23 PM NULL
1116 6/9/2010 9:36:09 AM PLATHKW 1256 0 1048 NULL 0 NULL 32229 TMP30032229 DELL-PC-GX150 Dell Optiplex GX150 DeskTop PC 2R5M011 8/10/2005 3:13:52 PM NULL
2699 7/19/2006 1:32:12 PM GINGRICHHC 1256 0 1036 NULL 0 NULL 40515 1060109395 DELL-PC-GX150 Dell Optiplex GX150 DeskTop PC CWCCC11 7/19/2006 1:32:12 PM NULL
1193 8/23/2005 2:50:27 AM MAGIC 1256 0 1054 NULL 0 NULL 32593 TMP30032593 COMPAQ I PAQ Compaq I Paq pocket PC 4G1CDW33M0T7 8/15/2005 1:37:37 PM NULL
1195 8/23/2005 2:50:28 AM MAGIC 1256 0 1057 NULL 1 8/19/2005 10:43:24 AM 32939 1060000077 DELL-PC-GX150 Dell Optiplex GX150 DeskTop PC 8X5M011 8/18/2005 2:07:40 PM NULL
1117 8/23/2005 2:50:37 AM MAGIC 1256 0 1048 NULL 0 NULL 32310 TMP30032310 DELL-PC-GX150 Dell Optiplex GX150 DeskTop PC 2Y7Y401 8/10/2005 3:14:39 PM NULL
1118 8/23/2005 2:50:37 AM MAGIC 1256 0 1048 NULL 0 NULL 32300 TMP30032300 PHILIPS 17" Philips 17" Monitor 32117028 8/10/2005 3:17:03 PM NULL
1196 11/9/2006 10:00:26 AM GENOEMG 1273 0 1068 NULL 1 11/9/2006 10:00:21 AM 26976 1011000771 NOK-CELL-3285V Nokia 3285V Cellular Phone 08303977301 9/8/2005 4:19:13 PM NULL
1246 9/16/2005 2:25:23 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 42859 1011004007 NOK-CELL-3285V Nokia 3285V Cellular Phone 03805218376 9/16/2005 2:25:23 PM NULL
1259 10/26/2005 8:49:30 AM CHRISTIELI 1416 0 1082 NULL 1 10/26/2005 8:49:25 AM 42888 1011004012 NOK-CELL-3285V Nokia 3285V Cellular Phone 07407824719 9/21/2005 1:15:32 PM NULL
1268 1/16/2006 1:11:01 PM CHRISTIELI 1416 0 1085 NULL 1 1/16/2006 1:11:00 PM 31423 1011000629 AUD-CP-CDM8200 Audiovox CDM-8200 Cellular Phone 09703223031 9/26/2005 1:26:53 PM NULL
1311 9/11/2007 3:02:53 PM CHRISTIELI 1416 0 1099 NULL 1 9/11/2007 3:02:49 PM 39873 1011003173 NOK-CELL-3586I NOKIA 3586I CELLULAR PHONE 04313432722 10/12/2005 4:58:56 PM NULL
1241 1/16/2006 2:02:15 PM CHRISTIELI 1416 0 1078 NULL 1 1/16/2006 2:02:13 PM 42684 1011004001 NOK-CELL-3586I NOKIA 3586I Cellular Phone 04401416697 9/15/2005 2:48:31 PM NULL
1242 11/22/2005 10:28:20 AM CHRISTIELI 1416 0 1078 NULL 1 11/22/2005 10:28:17 AM 42811 1011004005 NOK-CELL-3285V Nokia 3285V Cellular Phone 08303978079 9/15/2005 2:48:50 PM NULL
1243 1/16/2006 1:53:17 PM CHRISTIELI 1416 0 1078 NULL 1 1/16/2006 1:53:15 PM 26993 1011000752 NOK-CELL-3285V Nokia 3285V Cellular Phone 08303467731 9/15/2005 2:50:28 PM NULL
1244 9/16/2005 2:20:22 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 42683 1011004000 NOK-CELL-3285V Nokia 3285V Cellular Phone 08300874211 9/16/2005 2:20:22 PM NULL
1245 9/16/2005 2:20:43 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 39854 1011000718 NOK-CELL-3285V Nokia 3285V Cellular Phone 08300874100 9/16/2005 2:20:43 PM NULL
1247 9/16/2005 2:26:12 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 38104 1011000626 NOK-CELL-3586I NOKIA 3586I Cellular Phone 03808247570 9/16/2005 2:26:12 PM NULL
1249 9/16/2005 2:35:30 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 38095 1011000632 NOK-CELL-3586I NOKIA 3586I Cellular Phone 03808247527 9/16/2005 2:35:30 PM NULL
1251 9/16/2005 2:36:30 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 37374 1011000630 NOK-CELL-3586I NOKIA 3586I Cellular Phone 03805218382 9/16/2005 2:36:30 PM NULL
1252 9/16/2005 2:37:01 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 38101 1011000627 NOK-CELL-3586I NOKIA 3586I Cellular Phone 03808248465 9/16/2005 2:37:01 PM NULL
1257 10/26/2005 9:04:03 AM CHRISTIELI 1416 0 1081 NULL 1 10/26/2005 9:04:01 AM 26967 1011000750 NOK-CELL-3285V Nokia 3285V Cellular Phone 08303630666 9/20/2005 4:07:14 PM NULL
1258 10/26/2005 8:49:11 AM CHRISTIELI 1416 0 1082 NULL 1 10/26/2005 8:49:07 AM 42887 1011004010 NOK-CELL-3285V Nokia 3285V Cellular Phone 08303467554 9/21/2005 1:14:15 PM NULL
1260 9/22/2005 10:03:08 AM CHRISTIELI 1416 0 1083 NULL 0 NULL 31415 1011000730 NOK-CELL-3285V Nokia 3285V Cellular Phone 08300872010 9/22/2005 10:03:09 AM NULL
1263 11/22/2005 10:29:02 AM CHRISTIELI 1416 0 1085 NULL 1 11/22/2005 10:29:00 AM 31373 1011000636 AUD-CP-CDM8200 Audiovox CDM-8200 Cellular Phone 09703014965 9/26/2005 11:33:18 AM NULL
1264 1/16/2006 1:34:53 PM CHRISTIELI 1416 0 1085 NULL 1 1/16/2006 1:34:51 PM 39962 1011003177 AUD-CP-CDM8200 Audiovox CDM-8200 Cellular Phone 09703193839 9/26/2005 11:55:07 AM NULL
1269 1/16/2006 1:01:55 PM CHRISTIELI 1416 0 1085 NULL 1 1/16/2006 1:01:52 PM 31300 1011000760 AUD-CP-CDM8200 Audiovox CDM-8200 Cellular Phone 09703285588 9/26/2005 2:29:09 PM NULL
1270 1/16/2006 1:52:04 PM CHRISTIELI 1416 0 1078 NULL 1 1/16/2006 1:50:34 PM 27994 1011000675 NOK-CELL-3586I NOKIA 3586I Cellular Phone 04308926653 9/26/2005 4:26:43 PM NULL
1276 3/27/2008 8:59:05 AM DAVIDSONCS 1416 0 1092 NULL 1 3/27/2008 8:59:03 AM 43036 1011004017 SAMSUNG660 Samsung Cell Phone 660 04112926318 10/3/2005 12:09:01 PM NULL
1248 9/16/2005 2:34:44 PM CHRISTIELI 1416 0 1079 NULL 0 NULL 38467 1011000625 NOK-CELL-3586I NOKIA 3586I Cellular Phone 04308926732 9/16/2005 2:34:44 PM NULL
17024 6/9/2010 11:06:02 AM PLATHKW 1273 0 1050 NULL 0 NULL 43544 1002000058 PAG-MOT-BELL Motorola Bell Pager E002353006 6/9/2010 11:06:01 AM (___)___-____
17025 6/9/2010 11:06:11 AM PLATHKW 1273 0 1050 NULL 0 NULL 43545 1002000059 PAG-MOT-BELL Motorola Bell Pager E002340086 6/9/2010 11:06:11 AM NULL

...sorry

kezmaster
Starting Member

16 Posts

Posted - 2010-06-11 : 15:11:28

sorry to sound pushy, but any further ideas on this issue?
I really need a clever / solid solution.

Kevin W. Plath (c) J
Sr Programmer | Database Administrator | Contractor
DLCI 6.6 | 2 ASG Petawawa | the Adga Groupe
Défense nationale | National Defence
Petawawa, Canada K8H 2X3
Kevin.Plath@forces.gc.ca
Telephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969
Gouvernement du Canada | Government of Canada

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources