CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
JRadical
JRadical

Reputation: 1

SQL Server trigger on a procedure failing

I am trying to trigger a stored procedure to take the inserted values into my stored procedure as parameters and it is not letting me.

My table flow goes like this: a patient's history information will be inserted (HISTORY_APPOINTMENTS) and if at the time the patient has a column value of HasSuicidalThoughts = 'Y' I want the trigger to send the inserted patients information into a table I created called SuicideWatchLog.

First I created the table:

/* Table Creation for SuicideWatch Log*/
CREATE TABLE SuicideWatchLog
(
    logNum integer IDENTITY(1,1) NOT NULL PRIMARY KEY,
    PatientStudy# integer FOREIGN KEY References Patients(PatientStudy#),
    PatientName varchar(20),
    [Date] date,
    Dr# integer FOREIGN KEY References DOCTORS(Dr#),
    DaysinStudy integer
)

Next I created the procedure:

CREATE PROCEDURE AddSuicideWatch
    @PatientStudy# integer,
    @PatientName varchar(20),
    @Date date,
    @Dr# integer,
    @DaysinStudy integer
AS
BEGIN
    INSERT INTO SuicideWatchLog(PatientStudy#, Date, Dr#)
        (SELECT PatientStudy#, ApptDate, Dr#
         FROM APPOINTMENTS
         WHERE @PatientStudy# = PatientStudy#
           AND @Date = ApptDate
           AND @Dr# = Dr#)

    INSERT INTO SuicideWatchLog(PatientName, DaysinStudy)
        (SELECT PatientFName, datediff(day,StudyStartDate,getdate())
         FROM PATIENTS
         WHERE @PatientName = PatientFName
           AND @DaysinStudy = datediff(day,StudyStartDate,getdate()))
END

Finally I created the trigger:

CREATE TRIGGER SuicidalPatient
ON HISTORY_APPOINTMENT
AFTER INSERT
AS
    EXEC AddSuicideWatch(
        SELECT (I.PatientStudy#, P.PatientFName, A.ApptDate,  
        FROM INSERTED I
        JOIN APPOINTMENTS A ON I.Appt# = A.Appt#
        JOIN PATIENTS P ON I.PatientStudy# = P.PatientStudy#)

I expected this to allow me to send the inserted values into the stored procedure to trigger the creation of the log, but instead I am getting an error that is telling me my parameters aren't being found.

Is this an issue with the select statement, or is it a problem with the procedure itself?

Upvotes: 0

Views: 53

Answers (2)

masoud
masoud

Reputation: 478

you cant pass table to sp , but i know 2 ways for that : 1- use user defined type like that :

create type NewTable AS table (PatientStudy# int, PatientFName nvarchar(max), ApptDate date)

and the insert into NewTable Then call sp 

declare @TempTable NewTable
insert into  @TempTable(PatientStudy# , PatientFName , ApptDate)
select I.PatientStudy#, P.PatientFName, A.ApptDate,  
        FROM INSERTED I
        JOIN APPOINTMENTS A ON I.Appt# = A.Appt#
        JOIN PATIENTS P ON I.PatientStudy# = P.PatientStudy#

EXEC AddSuicideWatch( @TempTable)

and of course you should edit your SP :

CREATE PROCEDURE AddSuicideWatch
    @Table NewTable
AS
BEGIN
    INSERT INTO SuicideWatchLog(PatientStudy#, Date, Dr#)
        SELECT PatientStudy#, ApptDate, Dr#
         FROM APPOINTMENTS A join @Table T ON
           A.PatientStudy# = T.PatientStudy#
           A.Date = T.ApptDate
           A.Dr# = D.Dr#

    INSERT INTO SuicideWatchLog(PatientName, DaysinStudy)
        (SELECT PatientFName, datediff(day,StudyStartDate,getdate())
         FROM PATIENTS P join @Table T ON
         T.PatientName = P.PatientFName
           AND A.DaysinStudy = datediff(day,StudyStartDate,getdate()))
END

And the seccond way : just pass the primary key to sp and handle other things in sp

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89191

Is this an issue with the select statement, or is it a problem with the procedure itself?

Your stored procedure accepts scalar parameters. You can't pass a whole resultset to it. You can:

1) Integrate the INSERTs directly into the trigger body, eliminating the stored procedure.

2) Open a cursor over the query in the trigger, and loop through the rows, calling the stored procedure fore each one.

3) Declare a User-Defined Table Type matching the query result rows, declare and load an instance of the table type in the trigger body, and change the stored procedure to accept a Table-Valued Parameter.

Upvotes: 2

Related Questions