Delete trigger and getting field from another table

debugcn Published at Dev

Tom S

I have this delete trigger on an SQL database. The record deletes currently and gets written to an audit table. I have been asked to include in this history table a field from another table that is related to the record being deleted based on SurveyID. I thought I could do something like

select @Status = Status from table where Survey = deleted.Survey

But this is incorrect syntax.

ALTER trigger [dbo].[table_Selfdelete]
on [dbo].[table]

after delete
as
Begin
Set nocount on;

Declare @SurveyId int
Declare @StudentUIC varchar(10)
Declare @Status varchar(10)

select @SurveyId = deleted.SurveyID,
        @StudentUIC = deleted.StudentUIC
from deleted

select @Status = Status from tbly when SurveyID = deleted.SurveyID

insert into fupSurveyAudit
    values(@SurveyId,@StudentUIC,@Status)


End

Gordon Linoff

Arrgh. I think you want this insert in your trigger (and nothing else):

insert into fupSurveyAudit(SurveyId, StudentUIC, status)
    select d.SurveyId, d.StudentUIC, y.status
    from deleted d left join
         tbly y
         on d.SurveyId = y.SurveyId;

Notes:

deleted could contain more than one row, so assuming that it has one row can lead to a run-time error or incorrect results.
A left join is needed in case there is no matching row for the status.
You should always include the columns in an insert
Your archive table should have additional columns, such as an identity column and the date of the insert, which are set automatically (and hence not explicitly part of the insert).

Collected from the Internet

Please contact [email protected] to delete if infringement.