[RESOLVED]Inserting current date into a table..

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

Transact-SQL (2005)

[RESOLVED]Inserting current date into a table..

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-15 : 05:21:00

Hey all

I have two tables,

LCData(ID, FName, SName, CaptureDate etc)
LCPictures(ID, length, picture, type)

I would like to update LCData.CaptureDate to getdate where LCPictures.ID=LCData.ID

I am trying to insert current date into the LCData table in the CaptureDate field as soon as the corresponding photograph for that person has been inserted into the LCPictures table.

I've tried all sorts...INSERT INTO, UPDATE..Tried the following:

UPDATE tbl_LCData
SET CaptureDate=(SELECT getdate()
FROM tbl_LCData, tbl_LCPictures
WHERE tbl_LCData.ID=tbl_LCPictures.ID)
SELECT CaptureDate
FROM tbl_LCData

This gives me an error message:

[OLE DB Source [194]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.".

Any ideas?? All help will be highly appreciated :-)

Thanks,

Rupa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 05:29:41

[code]UPDATE t
SET t.CaptureDate = GETDATE()
FROM tbl_LCData AS t
INNER JOIN tbl_LCPictures AS s ON s.ID = t.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-15 : 05:45:04

Hey Peso

Many thanks, that worked but it inserts CaptureDate into all the data where s.ID=t.ID, I want it on the inserted one only. So basically if a picture for person 1234 is inserted, I would like it to update CaptureDate for person 1234 in the Data table.

I think I have to use the term 'inserted' somewhere?

Your help is greatly appreciated.

Many thanks,

Rupa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 05:51:24

[code]CREATE TRIGGER trgKeepMyCaptureDateUpToDate
ON LCPictures
AFTER INSERT
AS

SET NOCOUNT ON

UPDATE t
SET t.CaptureDate = GETDATE()
FROM tbl_LCData AS t
INNER JOIN inserted AS i ON i.ID = t.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-15 : 06:03:52

Thank you again Peso but it doesn't work?

It executed ok as well. No errors...

Any ideas??

Thanks,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-15 : 06:53:06

Hey again

Does it matter if we use inner join when tbl_LCData has IDNo rather than ID??

Just noticed..I have so many tables so I had changed it to:

CREATE TRIGGER trgKeepMyCaptureDateUpToDate
ON LCPictures
AFTER INSERT
AS

SET NOCOUNT ON

UPDATE t
SET t.CaptureDate = GETDATE()
FROM tbl_LCData AS t
INNER JOIN inserted AS i ON i.ID = t.IDNo

Does this matter??

Thanks,

Rupa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 07:12:52

We can't tell.
You are the one with the knowledge about your system.

E 12°55'05.25"
N 56°04'39.16"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-15 : 07:15:52

No I meant to say does it matter if we use inner join when one's primary key is 'ID' and the other is 'IDNo'?

Thanks,

Rupa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 07:18:06

Most probably it will matter.
You sould use the foreign key in inserted table, the column that references an unique/primary column in LCData table.

E 12°55'05.25"
N 56°04'39.16"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-15 : 07:31:24

Thanks Peso

I really appreciate your quick response.

The foreign key in inserted table is 'ID' and the foreign key in LCData table is 'IDNo'.

I should have mentioned this earlier. My apologies.

Many thanks,

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 12:00:45

quote:
Originally posted by Rupa

Thanks Peso

I really appreciate your quick response.

The foreign key in inserted table is 'ID' and the foreign key in LCData table is 'IDNo'.

I should have mentioned this earlier. My apologies.

Many thanks,

Rupa

what you could've done is to post some sample data so that we can understand how your tables are related. as we dont know your system it would be very hard to suggest what join condition should be. SO in future please provide some sample data from your tables specifying fields which will provide us with lot more help than a big paragraph of verbal explanation.

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-18 : 04:33:54

Thanks Visakh, here's some sample data:

tbl_LCData:
IDNo, FName, SName, CaptureDate
147, Sample, Data, 18/08/2008
251, Sample1, Data1,

tbl_LCPictures
ID, length, picture, type
147, 34442, Binary Data, 1
251, 30126, Binary Data, 1

Pictures are automatically inserted into tbl_LCPictures and data is automatically inserted into tbl_LCData. I would like to set CaptureDate=getdate() where inserted (tbl_LCPictures).ID=tbl_LCData.IDNo

So, basically when a picture for person 251 in inserted, I would like the tbl_LCData.CaptureDate to be set to getdate() for IDNo 251 in tbl_LCData

Hope this makes it a little clear. All replies are highly appreciated.

Many thanks,

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 04:47:56

Just make an inserted trigger on Pictures table which will do update on each insert.

CREATE TRIGGER YourTrigger
ON tbl_LCPictures
AFTER INSERT
AS 
UPDATE l
SET l.CaptureDate=GETDATE()
FROM tbl_LCData l
INNER JOIN INSERTED i
ON i.ID=l.IDNo
GO

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-18 : 04:58:36

Thanks Visakh

This is what Peso suggested but it doesn't work that's why my question arose whether it matters if we use inner join when both the tables do not have the same primary key (ID and IDNo).

Any ideas??

Thanks,

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 05:08:00

quote:
Originally posted by Rupa

Thanks Visakh

This is what Peso suggested but it doesn't work that's why my question arose whether it matters if we use inner join when both the tables do not have the same primary key (ID and IDNo).

Any ideas??

Thanks,

Rupa

Ok. Why didnt it work? What was the problem that you spotted? Even if columns ID & IDNo are not PK, if tables are related by them then it will not be a problem. But my question is at any point of them when insertion happens in pictures, will you have more than one record with same IDNo value in LCData? Then it will be a problem.You need to then find out a way to get the record corresponding to currently inserted record.

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-18 : 05:21:18

Sorry Visakh..it doesn't set the date to getdate(). When I open the tbl_LCData and look at the CaptureDate, it's null.

I run the task, ImportPictures and it runs successfully. Checked the data in the tbl_LCPictures and the record exists but the CaptureDate in tbl_LCData's is Null where as all the other fields have the correct data. I am baffled myself. Can't understand why. I have another trigger on tbl_LCData and that works fine. IDNo in tbl_LCData and ID in tbl_LCPictures are both primary keys and do not contain duplicate values.

I really appreciate your response.

Many thanks,

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 05:23:06

quote:
Originally posted by Rupa

Sorry Visakh..it doesn't set the date to getdate(). When I open the tbl_LCData and look at the CaptureDate, it's null.

I run the task, ImportPictures and it runs successfully. Checked the data in the tbl_LCPictures and the record exists but the CaptureDate in tbl_LCData's is Null where as all the other fields have the correct data. I am baffled myself. Can't understand why. I have another trigger on tbl_LCData and that works fine. IDNo in tbl_LCData and ID in tbl_LCPictures are both primary keys and do not contain duplicate values.

I really appreciate your response.

Many thanks,

Rupa

what is the other trigger on LC_data doing? is it an AFTER trigger or INSTEAD of trigger?

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-18 : 05:33:26

It's an after trigger:

AFTER UPDATE
AS

UPDATE t1
SET CardStatus = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101')
THEN 'Processed'
WHEN COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999) THEN 'Pending'
ELSE t1.CardStatus END, -- No change
CardStatusChanged = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101')
OR COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999)
THEN getdate()
ELSE t1.CardStatusChanged END
FROM tbl_LCData t1
JOIN inserted i
on t1.IDNo = i.IDNo
JOIN deleted AS D
ON t1.IDNo = D.IDNo

Could this be the conflict?

Thanks,

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 06:02:05

quote:
Originally posted by Rupa

It's an after trigger:

AFTER UPDATE
AS

UPDATE t1
SET CardStatus = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101')
THEN 'Processed'
WHEN COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999) THEN 'Pending'
ELSE t1.CardStatus END, -- No change
CardStatusChanged = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101')
OR COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999)
THEN getdate()
ELSE t1.CardStatusChanged END
FROM tbl_LCData t1
JOIN inserted i
on t1.IDNo = i.IDNo
JOIN deleted AS D
ON t1.IDNo = D.IDNo

Could this be the conflict?

Thanks,

Rupa

dont think so...I cant see this touching CaptureDate field at all. b/w can you just disable it for testing purpose and see if its working now as expected?

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-18 : 06:11:16

Tried that..Still doesn't work :-(

Thanks,

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 06:25:40

Ok.How will the insert be happening. will it be hapening simultaneously on Pictures and LCdata or one after the other?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources