Delete trigger and getting field from another table

Question

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    

Answer 1

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).

Answer 2

Triggers are fired once for each statement (Delete,insert,update) not for each row inside the statement.

You cannot use variables here because when multiple lines are deleted from the table only one line will be inserted in the Audit table because the variable can only hold one value.

You just need a simple insert from the deleted table into the Audit table something like this....

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

after delete
as
Begin
Set nocount on;

insert into fupSurveyAudit(SurveyId, StudentUIC,[Status])
select  d.SurveyID
       ,d.StudentUIC
       ,y.[Status]
from deleted d
INNER JOIN tbly y ON y.SurveyID = deleted.SurveyID

End  

Answer 3

Try this

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

after delete
as
Begin
Set nocount on;

insert into fupSurveyAudit  -- Better listed the column list here
select
    d.SurveyID, d.StudentUIC, y.Status
from
    deleted d JOIN tbly y ON d.SurveyID = y.SurveyID

End