Updating Two tables with one Input SQL 2005 4

[chiplarsen]

Hi, I have a SQL 2005 database that has an Access 2003 front end. I am using linked tables in Access. What I am trying to do is have the user input data from Access into the SQL server database. The field that they are entering will be stored in one table, but I need it to populate another table with the same exact data. This will be something like the "Doctor of the Day." That doctor will be stored in multiple tables. Does anyone know how I can make this happen in SQL 2005. I have read some about this, but I do not know where to start. Thank you for your time.

Chip

 

[RiverGuy]

Write a trigger on the first SQL Server table to update the second table.

 

[chiplarsen]

Hi RiverGuy, I have never written a trigger. I have been reading about triggers, but was not sure how to do it. Do you have an example of how I would do this? I would assume that when the first table was update, that would fire the trigger? There would have to be a statement that would use that rows "patient id" to know which row to update in the other table. "patient id" would be my key between the two tables. I really appreciate your help.

Thank you, Chip

 

[Emtucifor]

CREATE TRIGGER TR_Table1_IU ON Table1 FOR INSERT, UPDATE
AS
IF EXISTS (SELECT 1 FROM Deleted) BEGIN -- is an update
   UPDATE T2
   SET T2.DoctorName = I.DoctorName
   FROM
      Inserted I
      INNER JOIN Table2 T2 ON I.PatientID = T2.PatientID
END
ELSE BEGIN -- is an insert
   INSERT Table2
   SELECT I.PatientID, I.DoctorName
   FROM Inserted I
END

You will want to read up on the Inserted and Deleted tables to understand what they do and how they related to INSERT (only Inserted table is populated), DELETE (only Deleted table is populated), and UPDATE (both tables are populated).

Also, pay careful attention to the fact that the trigger doesn't run once for each row affected, it runs only once per data modification. So you have to handle things in a set-based fashion as my above code demonstrates.

 

[Emtucifor]

I didn't write the trigger to handle delete because I have no idea what you'd want to do for that. Just keep in mind that detecting an update would require two statements: one to check the Inserted table for rows and one to check the Deleted table for rows. Above, I could check only the Deleted table because the trigger was not FOR DELETE.

 

[chiplarsen]

Thank you for example. I will go do some reading.

 

[chiplarsen]

I know this is a stupid question, but where is "I" defined in the example. Is the "I" something that you made up or does it have to used in that code? Sorry for my ignorance.

I did read about inserted and deleted and I understand how they are used with Triggers.

 

[gmmastros]

In triggers, there are 2 hidden/special tables. There is an "Inserted" table and a "Deleted" table. These are not real tables, but exist only for triggers. Both tables will have the exact same structure as the table the trigger is written for. The deleted table will contain rows that will be deleted, and the inserted table will be rows that will be inserted (sorry for overstating the obvious).

In the code Emtucifor shows, the I is called a table alias.

[tt][blue]
FROM
Inserted [!]As[/!] I
[/blue][/tt]

The AS keyword is optional. I usually use it because I think it makes the code clearer, but it's really a "preference kind of thing".

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chiplarsen]

I understand now. Thank you all for your help. Now I have to figure out how to actually associate the trigger with the field and how to make it fire. That is another lesson. Thank you again.

 

[gmmastros]

Triggers are not associated with a field. They are associated with a table.

Looking at the sample from Emtucifor again:

[tt]CREATE TRIGGER [blue]TR_Table1_IU[/blue] ON [green]Table1[/green] FOR [red]INSERT, UPDATE[/red]
[/tt]

The part in blue is just the name of the trigger. This can be anything you want, but it's best to use a consistent naming scheme. The part in green is the table that the trigger will fire for. The part in red is the action that causes the trigger to fire.

So, in this example, the code will run any time data is inserted or updated for the Table1 table.

BTW, I appreciate the star, and the thanks. But, please, I encourage you to also thank RiverGuy (for the trigger idea) and Emtucifor (for the code sample). I didn't really do much here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Never mind that last part, I see now that you already did that.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chiplarsen]

Thank you again for the great information. It is wonderful that people share their knowledge. I hope one day I can do the same for someone.

 

[chiplarsen]

If you do not mind me asking, what does "IF EXISTS (SELECT 1 FROM Deleted)" mean? I think it is checking to see if the record? exist then it does an update instead of insert. I am not sure what "Select 1 from deleted" is all about? Thank you again.

 

[chiplarsen]

Well, I jumped the gun. I figured out what Select 1 does or Select 2 or Select 3. It will return the number if the condition is met. Thanks again.

 

[markros]

The IF EXISTS (select 1 from Deleted) checks if there is at least one record in Deleted table which means it's an Update trigger.

It is the same as to write

IF EXISTS (select * from Deleted).

I read a blog a year+ ago that if exists (select 1 from ...) is better to use than if exists (select * from ..)

The performance difference, if any, would be negligable, but since this blog I use the same syntax as Erik just showed.

 

[Emtucifor]

The number selected doesn't mean anything. It could be SELECT -2284738. The important thing about EXISTS is that it returns true if at least one row is found, and false if there are no rows found. It also stops querying after one row is found (except in possibly rare cases but don't worry about that). So it really is a test for existence. You can read

IF EXISTS (SELECT 1 FROM Deleted)

as

IF TABLE Deleted CONTAINS ANY ROWS

 

[chiplarsen]

Thank you for the information. I have another question about this trigger. I got the trigger to parse with no problems. I went into my main table and added the IPS_DOC_OF_DAY on one of the records. I was hoping it would update the the same patient in the CENSUS table. It did not happen. I must have done something wrong. I am still a little foggy on how this is going to work.

CREATE TRIGGER TR_MAIN_ADMIT_IU ON MAIN_ADMIT_TABLE AFTER INSERT, UPDATE AS 
IF EXISTS (SELECT 1 FROM Deleted) BEGIN -- is an update   
UPDATE CURRENT_CENSUS   SET CURRENT_CENSUS.IPS_DOC_OF_DAY = MAIN_ADMIT_TABLE.IPS_DOC_OF_DAY  
FROM      Inserted MAIN_ADMIT_TABLE      INNER JOIN CURRENT_CENSUS ON MAIN_ADMIT_TABLE.PAT_ACCT_NBR = CURRENT_CENSUS.PAT_ACCT_NBR END 

ELSE
BEGIN -- is an insert   
INSERT CURRENT_CENSUS SELECT MAIN_ADMIT_TABLE.IPS_DOC_OF_DAY  FROM Inserted MAIN_ADMIT_TABLE

Can anyone get me back on track? Thank you

 

[markros]

I don't see a bug in this code except I would alias Inserted table as I instead of the long confusing name you used. With this long name, IMHO, this code is harder to understand and debug.

Also put some comments into the trigger code.

 

[markros]

Also, did you actually run this code, e.g. are you sure the Trigger is added?

 

[chiplarsen]

Please excuse my ignorance..I do not see where the code actually ran. This is what I just ran.

CREATE TRIGGER TR_MAIN_ADMIT_IU ON MAIN_ADMIT_TABLE AFTER INSERT, UPDATE AS 
IF EXISTS (SELECT 1 FROM Deleted) BEGIN -- is an update   
UPDATE CURRENT_CENSUS   SET CURRENT_CENSUS.IPS_DOC_OF_DAY = MAIN_ADMIT_TABLE.IPS_DOC_OF_DAY  
FROM  Inserted I  INNER JOIN CURRENT_CENSUS ON I.PAT_ACCT_NBR = CURRENT_CENSUS.PAT_ACCT_NBR END 
ELSE
BEGIN -- is an insert   
INSERT CURRENT_CENSUS SELECT MAIN_ADMIT_TABLE.IPS_DOC_OF_DAY  FROM Inserted I END

The code parsed, but I got these errors.
Msg 4104, Level 16, State 1, Procedure TR_MAIN_ADMIT_IU, Line 3
The multi-part identifier "MAIN_ADMIT_TABLE.IPS_DOC_OF_DAY" could not be bound.
Msg 4104, Level 16, State 1, Procedure TR_MAIN_ADMIT_IU, Line 8
The multi-part identifier "MAIN_ADMIT_TABLE.IPS_DOC_OF_DAY" could not be bound.
Msg 213, Level 16, State 1, Procedure TR_MAIN_ADMIT_IU, Line 8
Insert Error: Column name or number of supplied values does not match table definition.

How can I see if it ran? Thank you