SQL触发器可以自行删除

本文关键字：删除触发器 SQL | 更新日期: 2023-09-27 18:03:35

我有一些表，我已经安装了触发它们，像:Booking, BookingDetails, Reservation, ReservationDays

触发器接受插入和更新的值，并将它们插入到另一个数据库中的另一个名为(TransTBL)的表中当我的应用程序插入这些表(在一个按钮事件)触发器(Booking, BookingDetails和Reservation)只被触发，然后触发器(BookingDetails和ReservationDays)被删除，我不知道为什么?!

是什么原因导致的?

触发示例:

ALTER TRIGGER [dbo].[trigBooking]
   ON  [dbo].[Booking]
   AFTER UPDATE,INSERT,DELETE
AS 
declare @Params nvarchar(max)
declare @OldVals nvarchar(max)
declare @NewVals nvarchar(max)
declare @SQL nvarchar(max)
declare @spName nvarchar(MAX)
declare @odel bit
declare @ndel bit
BEGIN
declare @userID int 
set @userID  = (Select UserID from WatheerDBTest.dbo.Users where UserName = (Select SYSTEM_USER))
declare @ox bit
declare @nx bit
set @ox = cast((select i.Async from Deleted i) as bit)
set @nx = cast((select i.Async from Inserted i) as bit)
if (@nx = 0) Or not exists (select * from inserted)
BEGIN

        SET NOCOUNT ON;
    SELECT @Params = COALESCE(@Params + ',@', '@') + COLUMN_NAME
    FROM  information_schema.columns where table_name = 'Booking'
    IF EXISTS(SELECT * FROM DELETED)
    BEGIN
    set @OldVals = IsNull(cast((Select  i.Booking_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_SNO from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_NO from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Date from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_BookedBy from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_FirstName from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_MiddleName from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Address from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_LastName from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.BookingStatus_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Country_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.City_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Phone from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_EMail from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Fax from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_CancelDate from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CancelReason_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CancelUser_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.VoidReason_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.VoidUser_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.NoShowReason_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.NoShowUser_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Remarks from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Displan_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.DisPlan_Value from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.MarkType_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Company_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CommType_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CommType_Value from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsNoShow from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsVoid from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.ApplySeasonChange from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsConfirmed from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsCancel from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_UserID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Folio_No from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Branch_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsLeaderPay from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.LeaderRes_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Account_ID from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.SpcialPrice from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Deleted from Deleted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Async from Deleted i) as nvarchar(max)), '')
    end
    else
    set @OldVals = ''
    set @NewVals = IsNull(cast((Select  i.Booking_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_SNO from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_NO from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Date from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_BookedBy from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_FirstName from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_MiddleName from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Address from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_LastName from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.BookingStatus_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Country_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.City_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Phone from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_EMail from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Fax from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_CancelDate from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CancelReason_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CancelUser_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.VoidReason_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.VoidUser_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.NoShowReason_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.NoShowUser_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_Remarks from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Displan_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.DisPlan_Value from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.MarkType_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Company_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CommType_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.CommType_Value from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsNoShow from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsVoid from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.ApplySeasonChange from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsConfirmed from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsCancel from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Booking_UserID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Folio_No from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Branch_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.IsLeaderPay from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.LeaderRes_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Account_ID from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.SpcialPrice from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Deleted from Inserted i) as nvarchar(max)), '') + ',' + 
        IsNull(cast((Select  i.Async from Inserted i) as nvarchar(max)), '')


    IF EXISTS(SELECT * FROM DELETED)
        BEGIN
      set @SQL =  'Update Booking Set Booking_ID = '+IsNull(Cast(((Select  i.Booking_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Booking_SNO = '+IsNull(Cast(( (Select  i.Booking_SNO from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Booking_NO = '+IsNull(Cast(( (Select  i.Booking_NO from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Booking_Date = '+IsNull(Cast(( (Select  i.Booking_Date from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Booking_BookedBy = '''+IsNull(Cast(( (Select  i.Booking_BookedBy from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Booking_FirstName = '''+IsNull(Cast(( (Select  i.Booking_FirstName from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Booking_MiddleName = '''+IsNull(Cast(( (Select  i.Booking_MiddleName from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Booking_Address = '''+IsNull(Cast(( (Select  i.Booking_Address from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Booking_LastName = '''+IsNull(Cast(( (Select  i.Booking_LastName from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
BookingStatus_ID = '+IsNull(Cast(( (Select  i.BookingStatus_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Country_ID = '+IsNull(Cast(( (Select  i.Country_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
City_ID = '+IsNull(Cast(( (Select  i.City_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Booking_Phone = '''+IsNull(Cast(( (Select  i.Booking_Phone from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Booking_EMail = '''+IsNull(Cast(( (Select  i.Booking_EMail from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Booking_Fax = '''+IsNull(Cast(( (Select  i.Booking_Fax from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Booking_CancelDate = '+IsNull(Cast(( (Select  i.Booking_CancelDate from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
CancelReason_ID = '+IsNull(Cast(( (Select  i.CancelReason_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
CancelUser_ID = '+IsNull(Cast(( (Select  i.CancelUser_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
VoidReason_ID = '+IsNull(Cast(( (Select  i.VoidReason_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
VoidUser_ID = '+IsNull(Cast(( (Select  i.VoidUser_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
NoShowReason_ID = '+IsNull(Cast(( (Select  i.NoShowReason_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
NoShowUser_ID = '+IsNull(Cast(( (Select  i.NoShowUser_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Booking_Remarks = '''+IsNull(Cast(( (Select  i.Booking_Remarks from Inserted i) ) AS Nvarchar(MAX)), '') + ''', 
Displan_ID = '+IsNull(Cast(( (Select  i.Displan_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
DisPlan_Value = '+IsNull(Cast(( (Select  i.DisPlan_Value from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
MarkType_ID = '+IsNull(Cast(( (Select  i.MarkType_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Company_ID = '+IsNull(Cast(( (Select  i.Company_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
CommType_ID = '+IsNull(Cast(( (Select  i.CommType_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
CommType_Value = '+IsNull(Cast(( (Select  i.CommType_Value from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
IsNoShow = '+IsNull(Cast(( (Select  i.IsNoShow from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
IsVoid = '+IsNull(Cast(( (Select  i.IsVoid from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
ApplySeasonChange = '+IsNull(Cast(( (Select  i.ApplySeasonChange from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
IsConfirmed = '+IsNull(Cast(( (Select  i.IsConfirmed from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
IsCancel = '+IsNull(Cast(( (Select  i.IsCancel from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Booking_UserID = '+IsNull(Cast(( (Select  i.Booking_UserID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Folio_No = '+IsNull(Cast(( (Select  i.Folio_No from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Branch_ID = '+IsNull(Cast(( (Select  i.Branch_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
IsLeaderPay = '+IsNull(Cast(( (Select  i.IsLeaderPay from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
LeaderRes_ID = '+IsNull(Cast(( (Select  i.LeaderRes_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Account_ID = '+IsNull(Cast(( (Select  i.Account_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
SpcialPrice = '+IsNull(Cast(( (Select  i.SpcialPrice from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Deleted = '+IsNull(Cast(( (Select  i.Deleted from Inserted i) ) AS Nvarchar(MAX)), '') + ', 
Async = '+'''True''' + '
 Where Booking_ID = ' + IsNull(cast((Select  i.Booking_ID from Inserted i) AS nvarchar(MAX)), '')
        END
        else
      set @SQL =  'Insert into Booking(Booking_ID,Booking_SNO,Booking_NO,Booking_Date,Booking_BookedBy,Booking_FirstName,Booking_MiddleName,Booking_Address,Booking_LastName,BookingStatus_ID,Country_ID,City_ID,Booking_Phone,Booking_EMail,Booking_Fax,Booking_CancelDate,CancelReason_ID,CancelUser_ID,VoidReason_ID,VoidUser_ID,NoShowReason_ID,NoShowUser_ID,Booking_Remarks,Displan_ID,DisPlan_Value,MarkType_ID,Company_ID,CommType_ID,CommType_Value,IsNoShow,IsVoid,ApplySeasonChange,IsConfirmed,IsCancel,Booking_UserID,Folio_No,Branch_ID,IsLeaderPay,LeaderRes_ID,Account_ID,SpcialPrice,Deleted,Async) Values (' + IsNull(Cast(((Select  i.Booking_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Booking_SNO from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Booking_NO from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_Date from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_BookedBy from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_FirstName from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_MiddleName from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_Address from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_LastName from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + IsNull(Cast(( (Select  i.BookingStatus_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Country_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.City_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_Phone from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_EMail from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_Fax from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_CancelDate from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + IsNull(Cast(( (Select  i.CancelReason_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.CancelUser_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.VoidReason_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.VoidUser_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.NoShowReason_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.NoShowUser_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + CHAR(39) + IsNull(Cast(( (Select  i.Booking_Remarks from Inserted i) ) AS Nvarchar(MAX)), '') + CHAR(39) + ', ' 
 + IsNull(Cast(( (Select  i.Displan_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.DisPlan_Value from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.MarkType_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Company_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.CommType_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.CommType_Value from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.IsNoShow from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.IsVoid from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.ApplySeasonChange from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.IsConfirmed from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.IsCancel from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Booking_UserID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Folio_No from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Branch_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.IsLeaderPay from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.LeaderRes_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Account_ID from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.SpcialPrice from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + IsNull(Cast(( (Select  i.Deleted from Inserted i) ) AS Nvarchar(MAX)), '') + ', ' 
 + '''True'''  + ')'
 -- IF row was deleted
set @odel = cast((select i.Deleted from Deleted i) AS bit)
set @ndel = cast((select i.Deleted from Inserted i) AS bit)
if  @odel <> @ndel
begin
set @spName = N'Deleted'
set @NewVals = NULL
end
else if  EXISTS(select * from Deleted)
begin
set @spName = N'Updated'
end
else if  EXISTS(select * from Inserted)
begin
set @spName = N'Added'
end
--  Getting user defind primary column value 
DECLARE @PKUser nvarchar(max), @sSQL nvarchar(max), @ParmDefinition nvarchar(max), @col nvarchar(max)
set @col = cast((select TableDefColumn from TransSampleDB.dbo.sysTables where TableName = 'Booking') as nvarchar)
SET @ParmDefinition = N'@PKUserOUT nvarchar(max) OUTPUT';
SELECT @sSQL = N'SELECT @PKUserOUT = ' + @col + ' FROM WatheerDBTest.dbo.Booking Where Booking_ID = ' + Cast(((Select  i.Booking_ID from Inserted i) ) AS Nvarchar(MAX))
EXEC sp_executesql @sSQL, @ParmDefinition, @PKUserOUT=@PKUser OUTPUT;

    insert into TransSampleDB.dbo.TransTBL_Local
    values (
    @spName,
    @Params,
    @OldVals,
    @NewVals,
    @SQL,
    Cast(((Select  i.Booking_ID from Inserted i) ) AS Nvarchar(MAX)),
    @PKUser,
    (Select Top 1 table_catalog from information_schema.tables as nvarchar),
    'Booking',
    getdate(),
    0,
    '',
    0,
    '1/1/1900',
    0,
    @userID
    )
    END
End 

 /************************************* End of Booking trigger *************************************/

SQL触发器可以自行删除

使用SSMS中的模式更改历史报告来查看何时以及由谁删除的:右键单击SSMS中的数据库，Reports/Standard Reports/schema changes history

我看到过类似的事情发生，当有人忘记在他们的触发器定义之后放一个GO，他们的脚本的下一部分做了某种类型的下降。像这样:

create table T (ID int not null)
go
create table V (ID int not null)
go
create table U (ID int not null)
go
if exists(select * from sys.objects where name='T_I')
begin
    drop trigger T_I
end
go
create trigger T_I on T
instead of insert
as
    insert into V (ID) select ID from inserted
--<-- No GO here!
if exists(select * from sys.objects where name='U_I')
begin
    drop trigger U_I
end
go
create trigger U_I on U
instead of insert
as
    insert into V (ID) select ID from inserted

现在使用第一个触发器:

select * from sys.triggers
go
insert into T(ID) values (1),(2)
go
select * from sys.triggers

结果:

T_I
U_I

然后

T_I