Insert and update trigger on sing tabble not work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Insert and update trigger on sing tabble not work

Author

Topic

ratulalamtvl
Starting Member

1 Post

Posted - 2013-11-24 : 00:57:04

Hi all,
I create inset and update and delete trigger on a single table but insert trigger also contain a update on same table.No error but update trigger not working
--tblBuyerInfo
CREATE TABLE tblBuyerInfo(
intBuyerID INT IDENTITY(1,1) NOT NULL,
strBuyerName VARCHAR(500) NOT NULL,
dtpEntryDate DATETIME NULL,
strCompanyId VARCHAR(200) NULL,
strActiveCode VARCHAR(250) NULL
)
ALTER TABLE tblBuyerInfo ADD CONSTRAINT tblBuyerInfo_intBuyerID PRIMARY KEY (intBuyerID);
ALTER TABLE tblBuyerInfo ADD CONSTRAINT tblBuyerInfo_strBuyerName UNIQUE (strBuyerName);
ALTER TABLE tblBuyerInfo ADD CONSTRAINT tblBuyerInfo_strActiveCode FOREIGN KEY (strActiveCode) REFERENCES lActiveAccount(strActiveCode);
---------tblActiveAccount
CREATE TABLE dbo.tblActiveAccount(
strActiveCode varchar(250) NOT NULL,
strTitle varchar(150) NULL,
dtCreationDate datetime NOT NULL,
mnyOpeningBalance money NULL,
strCompanyId varchar(200) NOT NULL,
)
ALTER TABLE dbo.tblActiveAccount FK_tblActiveAccount_tblCompany FOREIGN KEY(strCompanyId)REFERENCES dbo.tblCompany (strCompanyId)
----------------------------------------------------------------------------------------------------------
---Trigger is(on tblBuyerInfo)
ALTER TRIGGER CreateActiveAccForBuyerInsert
ON tblBuyerInfo
AFTER INSERT--, UPDATE, DELETE
AS
BEGIN

DECLARE @intRowCount INT;
SET @intRowCount=0;
DECLARE @strCompanyId varchar(200);
DECLARE @strControlCode varchar(200);
--SET @strControlCode ='-C1030706';
DECLARE @intActiveCode varchar(3);
DECLARE @strFinalActiveCode varchar(250);
DECLARE @strTitle varchar(1000);
DECLARE @intBuyerId int;
SELECT @strCompanyId=strCompanyId,@strTitle=strBuyerName,@intBuyerId=intBuyerID FROM INSERTED;
SELECT @strControlCode='-'+strBuyerContCode FROM tblLCControlHead WHERE strCompanyId=@strCompanyId
select @intRowCount=ISNULL(MAX(cast(T.intcode as int)),0) from(
Select right(strActiveCode,3) intcode from tblActiveAccount where strActiveCode LIKE @strCompanyId+@strControlCode+'%'
)T
IF @intRowCount =0
BEGIN
SET @intActiveCode='001';
END
ELSE
BEGIN
SET @intRowCount=@intRowCount+1;
IF LEN(@intRowCount)=1
BEGIN
SET @intActiveCode='00'+cast(@intRowCount as varchar(5));
END
ELSE IF LEN(@intRowCount)=2
BEGIN
SET @intActiveCode='0'+cast(@intRowCount as varchar(5));
END
ELSE
BEGIN
SET @intActiveCode=cast(@intRowCount as varchar(5));
END
END
SET @strFinalActiveCode=@strCompanyId+@strControlCode+@intActiveCode

INSERT INTO tblActiveAccount(strActiveCode, strTitle, dtCreationDate, mnyOpeningBalance, strNotes, IsCashBank, strCompanyId, strStatus, strAccountType, mnyDepreciationRate)--, mnyDepreciationAmount)
VALUES(@strFinalActiveCode,@strTitle,getdate(),0,'Created From Export at the time of buyer creation',0,@strCompanyId,'Active','DEBIT',0.0)

UPDATE tblBuyerInfo SET strActiveCode=@strFinalActiveCode where intBuyerID=@intBuyerId

END
GO
----------------------------------------------------------------------------------------------------------
ALTER TRIGGER UpdateActiveAccForBuyerUpdated
ON tblBuyerInfo
AFTER UPDATE
AS
BEGIN
DECLARE @strCompanyId varchar(200);
DECLARE @strFinalActiveCode varchar(3);
DECLARE @strTitle varchar(1000);
DECLARE @intBuyerId int;
SET NOCOUNT ON;
IF UPDATE (strBuyerName)
BEGIN
SELECT @strCompanyId=strCompanyId,@strTitle=strBuyerName,@strFinalActiveCode=strActiveCode FROM INSERTED;

UPDATE tblActiveAccount
SET strTitle=@strTitle
where strActiveCode=@strFinalActiveCode
AND strCompanyId=@strCompanyId
END
END
GO
----------------------------------------------------------------------------------------------------------
ALTER TRIGGER DeleteActiveAccForBuyerDeleted
ON tblBuyerInfo
AFTER Delete
AS
BEGIN
DECLARE @strCompanyId varchar(200);
DECLARE @strFinalActiveCode varchar(200);
SET NOCOUNT ON;
SELECT @strCompanyId=strCompanyId,@strFinalActiveCode=strActiveCode FROM DELETED;
DELETE FROM tblPeriodicBalance WHERE strActiveCode=@strFinalActiveCode
DELETE FROM tblActiveAccount
where strActiveCode=@strFinalActiveCode
AND strCompanyId=@strCompanyId
END
GO
-----------
I on trouble... pls help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 00:36:44

your trigger code has few issues

1. It assumes that temp tables INSERTED,DELETED contain only single row which is not true always. For batch operations it may contain multiple rows
2. UPDATE etc used inside Trigger code doesnt have a join to INSERTED table so it will cause entire rows in the table to be updated always.

try like below and see


ALTER TRIGGER CreateActiveAccForBuyerInsert
ON tblBuyerInfo
AFTER INSERT--, UPDATE, DELETE 
AS
BEGIN

INSERT INTO tblActiveAccount(strActiveCode, strTitle, dtCreationDate, mnyOpeningBalance, strNotes, IsCashBank, strCompanyId, strStatus, strAccountType, mnyDepreciationRate)
SELECT lh.strCompanyId + '-'+lh.strBuyerContCode + RIGHT('000'+ CAST(ISNULL(MAX(cast(right(aa.strActiveCode,3)as int)),1) AS varchar(5)),3),
i.strBuyerName,
getdate(),0,'Created From Export at the time of buyer creation',0,i.strCompanyId,'Active','DEBIT',0.0
FROM tblLCControlHead lh
INNER JOIN INSERTED i
ON i.strCompanyId = lh.strCompanyId
INNER JOIN tblActiveAccount aa
ON aa.strActiveCode LIKE lh.strCompanyId+'-'+lh.strBuyerContCode +'%'


UPDATE t
SET strActiveCode=i.strCompanyId + '-'+ih.strBuyerContCode + RIGHT('000'+ CAST(ISNULL(MAX(cast(right(aa.strActiveCode,3)as int)),1) AS varchar(5)),3)
FROM tblBuyerInfo bi
INNER JOIN INSERTED i
ON i.intBuyerID = bi.intBuyerID 
INNER JOIN tblLCControlHead lh
ON lh.strCompanyId = i.strCompanyId
INNER JOIN tblActiveAccount aa
ON aa.strActiveCode LIKE lh.strCompanyId+'-'+lh.strBuyerContCode +'%'

END
GO
----------------------------------------------------------------------------------------------------------
ALTER TRIGGER UpdateActiveAccForBuyerUpdated
ON tblBuyerInfo
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE (strBuyerName) 
BEGIN

UPDATE aa
SET strTitle= i.strBuyerName
FROM INSERTED i
JOIN tblActiveAccount aa
ON aa.strActiveCode= i.strActiveCode  
AND strCompanyId= i.strCompanyId
END
END
GO
----------------------------------------------------------------------------------------------------------
ALTER TRIGGER DeleteActiveAccForBuyerDeleted
ON tblBuyerInfo
AFTER Delete
AS
BEGIN
SET NOCOUNT ON;


DELETE pb
FROM tblPeriodicBalance pb
JOIN DELETED d
ON d.strActiveCode=d.strActiveCode 

DELETE aa
FROM tblActiveAccount aa
JOIN DELETED d
ON d.strActiveCode= d.strActiveCode  
AND aa.strCompanyId= d.strCompanyId
END
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources