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

 

[markros]

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 = I.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 I.IPS_DOC_OF_DAY  FROM Inserted I 
END

I fixed few typos.

 

[gmmastros]

You'll still (probably) have a problem here:

[tt][blue]
ELSE
BEGIN
-- is an insert
INSERT CURRENT_CENSUS
SELECT I.IPS_DOC_OF_DAY
FROM Inserted I

END
[/blue][/tt]

This code will insert a new row in to the CURRENT_CENSUS table. But... since you did not provide a column list, it expects to insert all columns.... but.... you are only selecting one column from the inserted table.

I have no idea what the tables are for, or what the column represent, so it's difficult to advise here. At a minimum, you need to make the number of columns match. Maybe this...

[tt][blue]
ELSE
BEGIN
-- is an insert
INSERT CURRENT_CENSUS(IPS_DOC_OF_DAY)
SELECT I.IPS_DOC_OF_DAY
FROM Inserted I

END

[/blue][/tt]

If there are other columns in this table, and all those columns allow nulls, this code should work just fine.

-George

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

 

[chiplarsen]

Thank you. I finally got the trigger to create itself. That will be a lot easier next time, thanks to all of you. One thing that I think I forgot to mention is that the CURRENT_CENSUS table will always have a matching Patient ID in the MAIN_ADMIT_TABLE. The only field that I am updating is the IPS_DOC_OF_DAY in the CURRENT_CENSUS table.

I am getting an error message when I try to update the IPS_DOC_OF_DAY in the MAIN_ADMIT_TABLE. I have attached the error message. I can not say it enough, how much I appreciate the help on teaching me this. Thank you.

 

[Emtucifor]

Put SET NOCOUNT ON at the top of your trigger and see if that makes a difference. If not, tell us the primary key and constraints on your IPS_DOC_OF_DAY table.

 

[chiplarsen]

I finally got it working. Thank you all for the help! This is what I ended up with.

USE [IPS_PROD]
GO
/****** Object:  Trigger [dbo].[TR_MAIN_ADMIT_IU]    Script Date: 10/14/2009 20:15:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_MAIN_ADMIT_IU] ON [dbo].[MAIN_ADMIT_TABLE] AFTER UPDATE AS 
IF EXISTS (SELECT 1 FROM Deleted) BEGIN -- is an update   
UPDATE CURRENT_CENSUS   SET CURRENT_CENSUS.IPS_DOC_OF_DAY = I.IPS_DOC_OF_DAY  FROM  Inserted I  
INNER JOIN CURRENT_CENSUS ON I.PAT_ACCT_NBR = CURRENT_CENSUS.PAT_ACCT_NBR AND I.IMPORT_DATE = CURRENT_CENSUS.CENSUS_DATE
END

 

[markros]

Since your trigger now for UPDATE only, you don't need a check IF EXISTS.