Trigger to insert into different database fails for user db

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)

Trigger to insert into different database fails for user db

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-05 : 10:41:21

Charles writes "I have an insert trigger that should insert information into another database to keep our patient records synced up. However, it fails each time for user dbo. the error says that user dbo does not have read permissions to the PATIENTS Table. My trigger is listed below.

CREATE TRIGGER tr_InsertMedScanPatient

ON mdpatdat

FOR INSERT
AS
BEGIN
-- Set up the variables
declare @PatID Int
declare @PatCode nchar(15)
declare @FirstName nchar(15)
declare @SSN nchar(9)
declare @MiddleName nchar(1)
declare @LastName nchar(20)
declare @DOB datetime

select @PatID = Max(PatID) from MEDSCN_PATIENT_VW

--Set the PatID to the next number
if @PatID is null
set @Patid = 1
else
set @Patid = @PatID + 1

select @FirstName = First_Name from Inserted
select @MiddleName = Middle_Initial from Inserted
select @LastName = Last_Name from Inserted
select @SSN = Social_Security_Number from Inserted
select @DOB = Birth_Date from Inserted
select @Phone = Phone from Inserted

--Insert the record into MedForce Patient Table
insert into MEDSCN_PATIENT_VW (PatID, PatCode, PatSSN, PatFNM, PatMNM, PatLNM, PatDOB)
VALUES
(@PatID, rtrim(@PatCode), rtrim(@SSN), rtrim(@FirstName), @MiddleName, rtrim(@LastName),
@DOB)

END"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-05 : 11:52:20

so GRANT SELECT ACCESS to the dbo.

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources