Need trigger help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Need trigger help

Author

Topic

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2007-03-06 : 15:59:14

Hello everyone,

Can anyone tell me the following trigger will update my table when I pressed the F5 or will it just compiled my query and execute whenever any insert or update done in the table?

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'XSMICMRawDataTRG'
AND type = 'TR')
DROP TRIGGER XSMICMRawDataTRG
GO

CREATE TRIGGER XSMICMRawDataTRG
ON xSMICMRawData
FOR INSERT, UPDATE
AS
BEGIN
PRINT 'XSMICMRawDataTRG'
IF UPDATE (PerClosed)
BEGIN
DECLARE @CommCalcPeriod CHAR(6),
@PerClosed CHAR(6),
@TranDate SMALLDATETIME,
@CpnyID CHAR(10),
@CustID CHAR (15),
@RefNbr CHAR(10),
@Acct CHAR(10),
@Division CHAR(10),
@RecordId INT,
@NATURE CHAR(1),
@ELEMENTCODE CHAR(4)

SELECT @CommCalcPeriod = CommCalcPeriod FROM xSMICMSetUp
SELECT @PerClosed = PerClosed, @TranDate = TranDate,
@CpnyID = CpnyID, @CustID = CustID, @RefNbr = RefNbr,
@Acct = Acct, @Division = Division, @RecordId = RecordId,
@NATURE = NATURE, @ELEMENTCODE = ELEMENTCODE
FROM INSERTED

IF @PerClosed = @CommCalcPeriod
BEGIN
DECLARE @FirstInvcDate SMALLDATETIME
SELECT @FirstInvcDate = FirstInvcDate FROM xSMICMCustomer
WHERE CpnyID = @CpnyID AND CustID = @CustID

IF @TranDate < @FirstInvcDate
BEGIN
DECLARE @LastDay SMALLDATETIME
DECLARE @Year CHAR(4)
DECLARE @Month CHAR(2)
DECLARE @ACTDATE SMALLDATETIME
SET @Year = SUBSTRING(@CommCalcPeriod, 1, 4)

SET @Month = SUBSTRING(@CommCalcPeriod, 5, 2)
SET @Month = CAST(@Month AS INT)

SET @ACTDATE = CAST((@Month + '/01/' + @Year) AS SMALLDATETIME)
SET @LastDay = DATEADD(m, 1, @ACTDATE)
SET @LastDay = DATEADD(D, -1, @ACTDATE)

UPDATE xSMICMRawData SET TranDateOrig = TranDate
WHERE CpnyID = @CpnyID AND CustID = @CustID
AND RefNbr = @RefNbr AND Acct = @Acct AND
Division = @Division AND RecordID = @RecordID
AND NATURE = @NATURE AND ELEMENTCODE = @ELEMENTCODE

UPDATE xSMICMRawData SET TranDate = @LastDay
WHERE CpnyID = @CpnyID AND CustID = @CustID
AND RefNbr = @RefNbr AND Acct = @Acct AND
Division = @Division AND RecordID = @RecordID
AND NATURE = @NATURE AND ELEMENTCODE = @ELEMENTCODE

END

END
END

END
GO

Why I am asking means When I tried to exec by selecting the query and press F5 this query is running and taking much time to compile.
What is the cause for this slow processing, does the table contains nore data and updating each one or anything else?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 21:12:32

Trigger will only execute when records is begin inserted or updated or deleted from the table.

"exec by selecting the query"
Which query ? the trigger script that you posted ? How long does it takes ?

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2007-03-07 : 10:11:47

Khtan,

This trigger takes more than 15 minutes in the client side. But we found that this delay happens because of network issue.

Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources