[CODE=sql]SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER [Cust_Audit] ON [dbo].[Customer]
INSTEAD OF INSERT
AS
DECLARE @CustNum int
DECLARE @CustomerCode INT,@CustomerId entificationTyp e INT
DECLARE @CustomerIdenti ficationGroupCo de CHAR(50),@Docum entNumber CHAR(50)
DECLARE @FirstName CHAR(50),@LastN ame CHAR(50),@Middl eName CHAR(50)
DECLARE @CustomerName CHAR(50),@Conta ctPerson CHAR(75),@Conta ctTitle CHAR(50)
DECLARE @Country INT,@ParishOrSt ate INT,@DistrictOr City INT,@CommunityO rTownShip INT
DECLARE @Street CHAR(75),@Telep honeNumbers CHAR(50),@CellP honeNumbers CHAR(50)
DECLARE @EmailAddress CHAR(50),@Photo Binary,@Custome rCategory INT
DECLARE @CustomerIdenti ficationIssuing Institution CHAR(85)
DECLARE @CustomerIdenti ficationIssuing Country INT,@CustomerId entificationExp irationDate DATETIME
DECLARE @CustomerIdenti ficationCategor y CHAR(50),@Comme nts nvarchar(500),@ Occupation CHAR(50),@Dateo fBirth DATETIME
DECLARE @TaxRegistratio nNumber CHAR,@LastTrade Date DATETIME, @LastTradeValue FLOAT
DECLARE @LastTradeType CHAR(50),@Trade rAssignedToAcco unt INT,@DiscountPu chaseTrade FLOAT
DECLARE @DiscountSaleTr ade FLOAT,@MinimumP uchaseTradeValu eForDiscount FLOAT
DECLARE @MinimumSaleTra deValueForDisco unt FLOAT,@Active BIT,@CompanyCod e INT,@LocationCo de INT
DECLARE @BranchCode INT,@OwnerCode INT,@DateTimeCr eated DATETIME,@DateT imeModified DATETIME
DECLARE @CreatedByUser INT,@ModifiedBy User INT,@SourceOfFu nds nvarchar(500)
Set @CustNum =(Select cc.CustomerCode From Customer cc, inserted i WHERE cc.CustomerCode = i.CustomerCode)
BEGIN
SET @CustomerCode = (select customercode from inserted)
SET @CustomerIdenti ficationType = (select CustomerIdentif icationType from inserted)
SET @CustomerIdenti ficationGroupCo de = (select CustomerIdentif icationGroupCod e from inserted)
SET @DocumentNumber = (select DocumentNumber from inserted)
SET @FirstName = (select FirstName from inserted)
SET @LastName = (select LastName from inserted)
SET @MiddleName = (select MiddleName from inserted)
SET @CustomerName =(select CustomerName from inserted)
SET @ContactPerson = (select ContactPerson from inserted)
SET @ContactTitle =(select ContactTitle from inserted)
SET @Country = (select Country from inserted)
SET @ParishOrState= (select ParishOrState from inserted)
SET @DistrictOrCity =(select DistrictOrCity from inserted)
SET @CommunityOrTow nShip =(select CommunityOrTown Ship from inserted)
SET @Street = (select Street from inserted)
SET @TelephoneNumbe rs = (select TelephoneNumber s from inserted)
SET @CellPhoneNumbe rs = (select CellPhoneNumber s from inserted)
SET @EmailAddress =(select EmailAddress from inserted)
SET @Photo = (select cast(Photo as binary) from inserted)
SET @CustomerCatego ry = (select CustomerCategor y from inserted)
SET @CustomerIdenti ficationIssuing Institution = (select CustomerIdentif icationIssuingI nstitution from inserted)
SET @CustomerIdenti ficationIssuing Country = (select CustomerIdentif icationIssuingC ountry from inserted)
SET @CustomerIdenti ficationExpirat ionDate =(select CustomerIdentif icationExpirati onDate from inserted)
SET @CustomerIdenti ficationCategor y = (select CustomerIdentif icationCategory from inserted)
SET @Comments = (select cast(Comments as nvarchar(500)) from inserted)
SET @Occupation = (select Occupation from inserted)
SET @DateofBirth = (select DateOfBirth from inserted)
SET @TaxRegistratio nNumber = (select TaxRegistration Number from inserted)
SET @LastTradeDate = (select LastTradeDate from inserted)
SET @LastTradeValue = (select LastTradeValue from inserted)
SET @LastTradeType = (select LastTradeType from inserted)
SET @TraderAssigned ToAccount = (select LastTradeType from inserted)
SET @DiscountPuchas eTrade = (select DiscountPuchase Trade from inserted)
SET @DiscountSaleTr ade = (select DiscountSaleTra de from inserted)
SET @MinimumPuchase TradeValueForDi scount = (select MinimumPuchaseT radeValueForDis count from inserted)
SET @MinimumSaleTra deValueForDisco unt = (select MinimumSaleTrad eValueForDiscou nt from inserted)
SET @Active = (select Active from inserted)
SET @CompanyCode = (select CompanyCode from inserted)
SET @LocationCode = (select LocationCode from inserted)
SET @BranchCode = (select BranchCode from inserted)
SET @OwnerCode = (select OwnerCode from inserted)
SET @DateTimeCreate d =(select DateTimeCreated from inserted)
SET @DateTimeModifi ed =(select DateTimeModifie d from inserted)
SET @CreatedByUser = (select CreatedByUser from inserted)
SET @ModifiedByUser = (select ModifiedByUser from inserted)
SET @SourceOfFunds = (select cast(SourceOfFu nds as nvarchar(500)) from inserted)
END
set nocount off
If ISNULL(@CustNum ,'') =''
BEGIN
INSERT INTO CUSTOMER(Custom erCode,Customer IdentificationT ype ,
CustomerIdentif icationGroupCod e,DocumentNumbe r,
FirstName,LastN ame,MiddleName,
CustomerName,Co ntactPerson,Con tactTitle,
Country,ParishO rState,District OrCity,Communit yOrTownShip,
Street,Telephon eNumbers,CellPh oneNumbers,
EmailAddress,Ph oto,CustomerCat egory,
CustomerIdentif icationIssuingI nstitution,
CustomerIdentif icationIssuingC ountry,Customer IdentificationE xpirationDate,
CustomerIdentif icationCategory ,Comments,Occup ation,DateofBir th,
TaxRegistration Number,LastTrad eDate, LastTradeValue,
LastTradeType,T raderAssignedTo Account,Discoun tPuchaseTrade,
DiscountSaleTra de,MinimumPucha seTradeValueFor Discount,
MinimumSaleTrad eValueForDiscou nt,Active,Compa nyCode,Location Code,
BranchCode,Owne rCode,DateTimeC reated,DateTime Modified,
CreatedByUser,M odifiedByUser,S ourceOfFunds)
values(@Custome rCode,@Customer IdentificationT ype,@CustomerId entificationGro upCode,@Documen tNumber,
@FirstName,@Las tName,@MiddleNa me,@CustomerNam e,@ContactPerso n,@ContactTitle ,
@Country,@Paris hOrState,@Distr ictOrCity,@Comm unityOrTownShip ,
@Street,@Teleph oneNumbers,@Cel lPhoneNumbers,@ EmailAddress,@P hoto,@CustomerC ategory,
@CustomerIdenti ficationIssuing Institution,@Cu stomerIdentific ationIssuingCou ntry,@CustomerI dentificationEx pirationDate,
@CustomerIdenti ficationCategor y,@Comments,@Oc cupation,@Dateo fBirth,
@TaxRegistratio nNumber,@LastTr adeDate, @LastTradeValue ,
@LastTradeType, @TraderAssigned ToAccount,@Disc ountPuchaseTrad e,
@DiscountSaleTr ade,@MinimumPuc haseTradeValueF orDiscount,
@MinimumSaleTra deValueForDisco unt,@Active,@Co mpanyCode,@Loca tionCode,
@BranchCode,@Ow nerCode,@DateTi meCreated,@Date TimeModified,
@CreatedByUser, @ModifiedByUser ,@SourceOfFunds )
END
IF @@ROWCOUNT <> 0
BEGIN
INSERT INTO
SysDatabase_Tra nsactions( Record_Id,Table Name,Operation, TransferDateTim e)
Select b.CustomerCode, 'Customer','I', Getdate()
From Inserted b
END
ELSE
BEGIN
UPDATE Customer
SET CustomerCode=@C ustomerCode,Cus tomerIdentifica tionType=@Custo merIdentificati onType ,
CustomerIdentif icationGroupCod e=@CustomerIden tificationGroup Code,DocumentNu mber=@DocumentN umber,
FirstName=@Firs tName,LastName= @LastName,Middl eName=@MiddleNa me,
CustomerName=@C ustomerName,Con tactPerson=@Con tactPerson,Cont actTitle=@Conta ctTitle,
Country=@Countr y,ParishOrState =@ParishOrState ,DistrictOrCity =@DistrictOrCit y,CommunityOrTo wnShip=@Communi tyOrTownShip,
Street=@Street, TelephoneNumber s=@TelephoneNum bers,CellPhoneN umbers=@CellPho neNumbers,
EmailAddress=@E mailAddress,Pho to=@Photo,Custo merCategory=@Cu stomerCategory,
CustomerIdentif icationIssuingI nstitution=@Cus tomerIdentifica tionIssuingInst itution,
CustomerIdentif icationIssuingC ountry=@Custome rIdentification IssuingCountry, CustomerIdentif icationExpirati onDate=@Custome rIdentification ExpirationDate,
CustomerIdentif icationCategory =@CustomerIdent ificationCatego ry,Comments=@Co mments,Occupati on=@Occupation, DateofBirth=@Da teofBirth,
TaxRegistration Number=@TaxRegi strationNumber, LastTradeDate=@ LastTradeDate,L astTradeValue=@ LastTradeValue,
LastTradeType=@ LastTradeType,T raderAssignedTo Account=@Trader AssignedToAccou nt,DiscountPuch aseTrade=@Disco untPuchaseTrade ,
DiscountSaleTra de=@DiscountSal eTrade,MinimumP uchaseTradeValu eForDiscount=@M inimumPuchaseTr adeValueForDisc ount,
MinimumSaleTrad eValueForDiscou nt=@MinimumSale TradeValueForDi scount,Active=@ Active,CompanyC ode=@CompanyCod e,LocationCode= @LocationCode,
BranchCode=@Bra nchCode,OwnerCo de=@OwnerCode,D ateTimeCreated= @DateTimeCreate d,DateTimeModif ied=@DateTimeMo dified,
CreatedByUser=@ CreatedByUser,M odifiedByUser=@ ModifiedByUser, SourceOfFunds=@ SourceOfFunds
WHERE CustomerCode = @CustNum
END
IF @@ROWCOUNT <> 0
BEGIN
INSERT INTO
SysDatabase_Tra nsactions( Record_Id,Table Name,Operation, TransferDateTim e)
Select b.CustomerCode, 'Customer','U', Getdate()
From Inserted b
END[/CODE]
The above code is within my trigger. The problem is that nothing is been inserted in the SysDatabase_Tra nsaction table. Can someone tell me what i be doing wrong
Thanks in advance
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER [Cust_Audit] ON [dbo].[Customer]
INSTEAD OF INSERT
AS
DECLARE @CustNum int
DECLARE @CustomerCode INT,@CustomerId entificationTyp e INT
DECLARE @CustomerIdenti ficationGroupCo de CHAR(50),@Docum entNumber CHAR(50)
DECLARE @FirstName CHAR(50),@LastN ame CHAR(50),@Middl eName CHAR(50)
DECLARE @CustomerName CHAR(50),@Conta ctPerson CHAR(75),@Conta ctTitle CHAR(50)
DECLARE @Country INT,@ParishOrSt ate INT,@DistrictOr City INT,@CommunityO rTownShip INT
DECLARE @Street CHAR(75),@Telep honeNumbers CHAR(50),@CellP honeNumbers CHAR(50)
DECLARE @EmailAddress CHAR(50),@Photo Binary,@Custome rCategory INT
DECLARE @CustomerIdenti ficationIssuing Institution CHAR(85)
DECLARE @CustomerIdenti ficationIssuing Country INT,@CustomerId entificationExp irationDate DATETIME
DECLARE @CustomerIdenti ficationCategor y CHAR(50),@Comme nts nvarchar(500),@ Occupation CHAR(50),@Dateo fBirth DATETIME
DECLARE @TaxRegistratio nNumber CHAR,@LastTrade Date DATETIME, @LastTradeValue FLOAT
DECLARE @LastTradeType CHAR(50),@Trade rAssignedToAcco unt INT,@DiscountPu chaseTrade FLOAT
DECLARE @DiscountSaleTr ade FLOAT,@MinimumP uchaseTradeValu eForDiscount FLOAT
DECLARE @MinimumSaleTra deValueForDisco unt FLOAT,@Active BIT,@CompanyCod e INT,@LocationCo de INT
DECLARE @BranchCode INT,@OwnerCode INT,@DateTimeCr eated DATETIME,@DateT imeModified DATETIME
DECLARE @CreatedByUser INT,@ModifiedBy User INT,@SourceOfFu nds nvarchar(500)
Set @CustNum =(Select cc.CustomerCode From Customer cc, inserted i WHERE cc.CustomerCode = i.CustomerCode)
BEGIN
SET @CustomerCode = (select customercode from inserted)
SET @CustomerIdenti ficationType = (select CustomerIdentif icationType from inserted)
SET @CustomerIdenti ficationGroupCo de = (select CustomerIdentif icationGroupCod e from inserted)
SET @DocumentNumber = (select DocumentNumber from inserted)
SET @FirstName = (select FirstName from inserted)
SET @LastName = (select LastName from inserted)
SET @MiddleName = (select MiddleName from inserted)
SET @CustomerName =(select CustomerName from inserted)
SET @ContactPerson = (select ContactPerson from inserted)
SET @ContactTitle =(select ContactTitle from inserted)
SET @Country = (select Country from inserted)
SET @ParishOrState= (select ParishOrState from inserted)
SET @DistrictOrCity =(select DistrictOrCity from inserted)
SET @CommunityOrTow nShip =(select CommunityOrTown Ship from inserted)
SET @Street = (select Street from inserted)
SET @TelephoneNumbe rs = (select TelephoneNumber s from inserted)
SET @CellPhoneNumbe rs = (select CellPhoneNumber s from inserted)
SET @EmailAddress =(select EmailAddress from inserted)
SET @Photo = (select cast(Photo as binary) from inserted)
SET @CustomerCatego ry = (select CustomerCategor y from inserted)
SET @CustomerIdenti ficationIssuing Institution = (select CustomerIdentif icationIssuingI nstitution from inserted)
SET @CustomerIdenti ficationIssuing Country = (select CustomerIdentif icationIssuingC ountry from inserted)
SET @CustomerIdenti ficationExpirat ionDate =(select CustomerIdentif icationExpirati onDate from inserted)
SET @CustomerIdenti ficationCategor y = (select CustomerIdentif icationCategory from inserted)
SET @Comments = (select cast(Comments as nvarchar(500)) from inserted)
SET @Occupation = (select Occupation from inserted)
SET @DateofBirth = (select DateOfBirth from inserted)
SET @TaxRegistratio nNumber = (select TaxRegistration Number from inserted)
SET @LastTradeDate = (select LastTradeDate from inserted)
SET @LastTradeValue = (select LastTradeValue from inserted)
SET @LastTradeType = (select LastTradeType from inserted)
SET @TraderAssigned ToAccount = (select LastTradeType from inserted)
SET @DiscountPuchas eTrade = (select DiscountPuchase Trade from inserted)
SET @DiscountSaleTr ade = (select DiscountSaleTra de from inserted)
SET @MinimumPuchase TradeValueForDi scount = (select MinimumPuchaseT radeValueForDis count from inserted)
SET @MinimumSaleTra deValueForDisco unt = (select MinimumSaleTrad eValueForDiscou nt from inserted)
SET @Active = (select Active from inserted)
SET @CompanyCode = (select CompanyCode from inserted)
SET @LocationCode = (select LocationCode from inserted)
SET @BranchCode = (select BranchCode from inserted)
SET @OwnerCode = (select OwnerCode from inserted)
SET @DateTimeCreate d =(select DateTimeCreated from inserted)
SET @DateTimeModifi ed =(select DateTimeModifie d from inserted)
SET @CreatedByUser = (select CreatedByUser from inserted)
SET @ModifiedByUser = (select ModifiedByUser from inserted)
SET @SourceOfFunds = (select cast(SourceOfFu nds as nvarchar(500)) from inserted)
END
set nocount off
If ISNULL(@CustNum ,'') =''
BEGIN
INSERT INTO CUSTOMER(Custom erCode,Customer IdentificationT ype ,
CustomerIdentif icationGroupCod e,DocumentNumbe r,
FirstName,LastN ame,MiddleName,
CustomerName,Co ntactPerson,Con tactTitle,
Country,ParishO rState,District OrCity,Communit yOrTownShip,
Street,Telephon eNumbers,CellPh oneNumbers,
EmailAddress,Ph oto,CustomerCat egory,
CustomerIdentif icationIssuingI nstitution,
CustomerIdentif icationIssuingC ountry,Customer IdentificationE xpirationDate,
CustomerIdentif icationCategory ,Comments,Occup ation,DateofBir th,
TaxRegistration Number,LastTrad eDate, LastTradeValue,
LastTradeType,T raderAssignedTo Account,Discoun tPuchaseTrade,
DiscountSaleTra de,MinimumPucha seTradeValueFor Discount,
MinimumSaleTrad eValueForDiscou nt,Active,Compa nyCode,Location Code,
BranchCode,Owne rCode,DateTimeC reated,DateTime Modified,
CreatedByUser,M odifiedByUser,S ourceOfFunds)
values(@Custome rCode,@Customer IdentificationT ype,@CustomerId entificationGro upCode,@Documen tNumber,
@FirstName,@Las tName,@MiddleNa me,@CustomerNam e,@ContactPerso n,@ContactTitle ,
@Country,@Paris hOrState,@Distr ictOrCity,@Comm unityOrTownShip ,
@Street,@Teleph oneNumbers,@Cel lPhoneNumbers,@ EmailAddress,@P hoto,@CustomerC ategory,
@CustomerIdenti ficationIssuing Institution,@Cu stomerIdentific ationIssuingCou ntry,@CustomerI dentificationEx pirationDate,
@CustomerIdenti ficationCategor y,@Comments,@Oc cupation,@Dateo fBirth,
@TaxRegistratio nNumber,@LastTr adeDate, @LastTradeValue ,
@LastTradeType, @TraderAssigned ToAccount,@Disc ountPuchaseTrad e,
@DiscountSaleTr ade,@MinimumPuc haseTradeValueF orDiscount,
@MinimumSaleTra deValueForDisco unt,@Active,@Co mpanyCode,@Loca tionCode,
@BranchCode,@Ow nerCode,@DateTi meCreated,@Date TimeModified,
@CreatedByUser, @ModifiedByUser ,@SourceOfFunds )
END
IF @@ROWCOUNT <> 0
BEGIN
INSERT INTO
SysDatabase_Tra nsactions( Record_Id,Table Name,Operation, TransferDateTim e)
Select b.CustomerCode, 'Customer','I', Getdate()
From Inserted b
END
ELSE
BEGIN
UPDATE Customer
SET CustomerCode=@C ustomerCode,Cus tomerIdentifica tionType=@Custo merIdentificati onType ,
CustomerIdentif icationGroupCod e=@CustomerIden tificationGroup Code,DocumentNu mber=@DocumentN umber,
FirstName=@Firs tName,LastName= @LastName,Middl eName=@MiddleNa me,
CustomerName=@C ustomerName,Con tactPerson=@Con tactPerson,Cont actTitle=@Conta ctTitle,
Country=@Countr y,ParishOrState =@ParishOrState ,DistrictOrCity =@DistrictOrCit y,CommunityOrTo wnShip=@Communi tyOrTownShip,
Street=@Street, TelephoneNumber s=@TelephoneNum bers,CellPhoneN umbers=@CellPho neNumbers,
EmailAddress=@E mailAddress,Pho to=@Photo,Custo merCategory=@Cu stomerCategory,
CustomerIdentif icationIssuingI nstitution=@Cus tomerIdentifica tionIssuingInst itution,
CustomerIdentif icationIssuingC ountry=@Custome rIdentification IssuingCountry, CustomerIdentif icationExpirati onDate=@Custome rIdentification ExpirationDate,
CustomerIdentif icationCategory =@CustomerIdent ificationCatego ry,Comments=@Co mments,Occupati on=@Occupation, DateofBirth=@Da teofBirth,
TaxRegistration Number=@TaxRegi strationNumber, LastTradeDate=@ LastTradeDate,L astTradeValue=@ LastTradeValue,
LastTradeType=@ LastTradeType,T raderAssignedTo Account=@Trader AssignedToAccou nt,DiscountPuch aseTrade=@Disco untPuchaseTrade ,
DiscountSaleTra de=@DiscountSal eTrade,MinimumP uchaseTradeValu eForDiscount=@M inimumPuchaseTr adeValueForDisc ount,
MinimumSaleTrad eValueForDiscou nt=@MinimumSale TradeValueForDi scount,Active=@ Active,CompanyC ode=@CompanyCod e,LocationCode= @LocationCode,
BranchCode=@Bra nchCode,OwnerCo de=@OwnerCode,D ateTimeCreated= @DateTimeCreate d,DateTimeModif ied=@DateTimeMo dified,
CreatedByUser=@ CreatedByUser,M odifiedByUser=@ ModifiedByUser, SourceOfFunds=@ SourceOfFunds
WHERE CustomerCode = @CustNum
END
IF @@ROWCOUNT <> 0
BEGIN
INSERT INTO
SysDatabase_Tra nsactions( Record_Id,Table Name,Operation, TransferDateTim e)
Select b.CustomerCode, 'Customer','U', Getdate()
From Inserted b
END[/CODE]
The above code is within my trigger. The problem is that nothing is been inserted in the SysDatabase_Tra nsaction table. Can someone tell me what i be doing wrong
Thanks in advance
Comment