Query help - 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 2000 Forums

Transact-SQL (2000)

Query help

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-03-30 : 14:24:40

Hi,

I have 2 tables

Table A
id, lastmodifieddate, colA, Colb,ColC,...............

Table B
id, lastmodifieddate, colA, Colb,ColC,...............

Table A
id lastmodifieddate
1 3/18/2007
2 3/2/2007
3 3/15/2007
4 3/20/2007

Table B
id lastmodifieddate
1 3/2/2007
2 3/2/2007
1 3/10/2007
3 3/10/2007
3 3/15/2007
1 3/18/2007
4 3/20/2007

By this for very change in Table A is captured in table B. Please let me way to insert the records in Table B

Here is my logic

select a.* from TableA a
inner join TableB B on a.[id] = B.[id]
where a.LastModifiedDate > (select max(B.LastModifiedDate) from TableB c1 where c1.[id] = a.[id])

I am getting error for having agg function in where.

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 15:11:21

Create a TRIGGER for tableA. An UPDATE/INSERT trigger. And even DELETE trigger?

CREATE TRIGGER trgTableA_Audit
FOR UPDATE, INSERT

INSERT TableB (ID, LastModifiedDate)
SELECT ID, CURRENT_TIMESTAMP FROM INSERTED

Peter Larsson
Helsingborg, Sweden

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-03-30 : 16:21:08

thanks for quick reply.

It is not possible to add trigger to that table because it updated by different application and my process don't have control.

Is it possible to write some query and to find the modified records and insert in Table B.

thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 16:27:02

You can check for which application that is inserting or updating the records with

SELECT APP_NAME()

If app is yours, execute the result in the trigger, if not, exit...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources