New to Triggers - 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

General SQL Server Forums

New to SQL Server Programming

New to Triggers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-16 : 08:04:15

Chris writes "New to Triggers

I'm working on updating records using a trigger.

My environment is:

Win 2000
SQL 2000 sp3

I have two tables CLIN_1 and CLIN_2. If there is an update to a record in CLIN_1
I want it to also be reflected in CLIN_2.

Note: Both tables have exact same records and CLIN_1 has a PK of CLIN_Code and
CLIN_2 has a PK of ID.

I have been able to run this somewhat successfully but it created new records in CLIN_2 (before the additional “If exists” and “Delete” statements)

At this point I'm attempting to either update the corresponding
record in CLIN_2 or create a new record and delete the old record from CLIN_2.

Currently receiving an “Invalid object name ‘CLIN_2.CLIN_Code’” error when executing this code.

Make sense?

Any suggestions?

Here's my code.

CREATE TRIGGER [CLIN_U]
ON dbo.CLIN_1
FOR UPDATE, INSERT
AS

/* sends record of updated items to CLIN_2 */

If exists (select 'true' from CLIN_2.CLIN_Code inner join CLIN_1 on Clin_1.CLIN_Code = CLIN_2.CLIN_Code
inner join inserted on CLIN_1.CLIN_Code = inserted.CLIN_Code)

Begin

Delete CLIN_2
From CLIN_2
Inner join CLIN_1 on Clin_1.CLIN_Code = CLIN_2.CLIN_Code
Inner join inserted on CLIN_2.CLIN_code = inserted.CLIN_code

End

Insert into dbo.CLIN_2
( CLIN_Code, CLIN, CLIN_desc, APPH, Schedule, Price, CLIN_Discount, CLIN_flag, update_date)

Select CLIN_1.CLIN_Code, CLIN_1.CLIN, CLIN_1.CLIN_desc, CLIN_1.APPH, CLIN_1.Schedule, CLIN_1.Price, CLIN_1.CLIN_Discount, CLIN_1.CLIN_Flag,
CLIN_1.update_date

From CLIN_1 inner join inserted on CLIN_1.CLIN_code = inserted.CLIN_code
"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 09:05:25

[code]If exists (select 'true' from CLIN_2~~.CLIN_Code~~ inner join CLIN_1 on Clin_1.CLIN_Code = CLIN_2.CLIN_Code
inner join inserted on CLIN_1.CLIN_Code = inserted.CLIN_Code)[/code]

----------------------------------
'KH'
Time is always against us

Frank V
Starting Member

5 Posts

Posted - 2006-02-16 : 13:34:46

I'm a newbie to SQL, but this looks similar to my question

How can I make a calculation on a given set of fields in a record as the record is written to the table?

Specifically, we have a set of test stands that are writing to the table. The frequency of when each stand writes logged data to the table can vary (every 10 seconds,every 30 seconds, etc.). The date and time when the data was logged are included as two fields. For each test stand I'd like to find the interval since the last time it wrote to the table. The interval calculation would occur every time a new row hit the table.

Any suggestions are greatly appreciated!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources