October 28, 2014 at 9:10 am
i have a table called tblevent on that i have written a trigger.before that when ever the data is loaded in tblevent immediately it fires the below trigger and updates the data in another table called tblCal
ALTER TRIGGER [dbo].[trU] ON [dbo].[tblEvent]
FOR INSERT
AS
Declare @CuID char(6),
@CuDesc char(40),
@CuDate datetime
SET NOCOUNT ON
Select @CuID = i.UID , @CuDesc=i.Desc, @CuDate=i.Date From Inserted i
If(@CuDesc !='available')
Begin
Update tblCal set avbl='Out', Desc=@CurDesc where cadate=@CuDate and UID=@CuID
ENd
SET NOCOUNT OFF
Any help why this trigger is not firing...the condition is satisfying there are lot of records with desc is not available..then why its not firing ?
October 28, 2014 at 9:19 am
You can check with the objectproperty function if the trigger is disabled. Also notice that your trigger won't handle correctly cases where you insert more then 1 record into the table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2014 at 9:35 am
Hi ,
how to check the object propert function?
October 28, 2014 at 9:41 am
mcfarlandparkway (10/28/2014)
Hi ,how to check the object propert function?
http://msdn.microsoft.com/en-us/library/ms176105.aspx
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2014 at 9:58 am
I checked the object property its enabled..is there any work around with proceedure?
Instead of trigger i am planning to write a procedure which we can run using job which will work same way as TRIGGER
with these two tables in the same way.
how can i do that?
here are my tables with column names
1.tblcal
ID(int,not null)
UID(varchar(10),null)
Desc(varchar(200),null)
Date(datetime,null)
avbl(varchar(5),null)
2.tblEvent
ID(int,notnull)
UID(varchar(10),null)
Desc(varchar(200),null)
Date(datetime,null)
Down is my trigger on tblEvent..
ALTER TRIGGER [dbo].[trU] ON [dbo].[tblEvent]
FOR INSERT
AS
Declare @CuID char(6),
@CuDesc char(40),
@CuDate datetime
SET NOCOUNT ON
Select @CuID = i.UID , @CuDesc=i.Desc, @CuDate=i.Date From Inserted i
If(@CuDesc !='available')
Begin
Update tblCal set avbl='Out', Desc=@CurDesc where cadate=@CuDate and UID=@CuID
ENd
SET NOCOUNT OFF
October 28, 2014 at 10:03 am
The trigger is firing. You must keep in mind that SQL Server only fires a trigger once per statement, no matter how many rows are INSERTed (UPDATEd or DELETEd).
Therefore, you need to use set-based processing in your triggers. For example, here is a re-write of your current trigger:
ALTER TRIGGER [dbo].[trU]
ON [dbo].[tblEvent]
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE c
SET avbl='Out', [Desc] = i.[Desc]
FROM tblCal c
INNER JOIN inserted i ON
i.Date = c.cadate AND
c.UID = i.UID
WHERE
i.[Desc] <> 'available'
GO --end of trigger
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 28, 2014 at 10:18 am
Thank you SSSCrazy.
I modified now plz look into it.
so you are saying no need to pass the paramters right?by using these two tables we are joining am i right?
ALTER TRIGGER [dbo].[tru] ON [dbo].[tblEvent]
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE c
SET avbl='Out', [Desc] = i.[Desc]
FROM tblCal c
INNER JOIN inserted i ON
i.Date = c.date AND
c.UID = i.UID
WHERE
i.[Desc] <> 'available'
ENd
SET NOCOUNT OFF
October 28, 2014 at 10:41 am
Yes, "Valued Member", that is basically correct, you do not use parameters like that in a trigger in SQL Server.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 28, 2014 at 10:43 am
Thank you so much for your valuable time and for information....
October 28, 2014 at 12:04 pm
Hi SSSCrazy,
I have another problem with Desc column.Desc which are going to be in and out Basically we need to update tblcal differently for different descriptions;in that case I don't think trigger is that reliable;Means for example for 10 Desc we need to update in and for other 10 we need to update out
Actually every thursday on the tblevent data is loaded once its loaded it fired a trigger and will update in tblcal.
but my client is looking for a procedure which we can schedule as a job after the tblevent entry done on Thursday.
How can i do with stored procedure?
October 28, 2014 at 3:16 pm
It sounds like you need something along these lines:
ALTER TRIGGER [dbo].[trU]
ON [dbo].[tblEvent]
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE c
SET avbl= CASE
WHEN i.[Desc] like '%out%' THEN 'Out'
WHEN I.[Desc] like '%in%' THEN 'In'
WHEN i.[Desc] = 'available' THEN 'some description'
--...
END,
[Desc] = i.[Desc] /*or also
CASE i.[Desc]
WHEN i.[Desc] = 'available' THEN 'some description'
WHEN i.[Desc] like '%out%' THEN 'some other description'
WHEN I.[Desc] like '%in%' THEN 'yet another description'
--...
END
*/
FROM tblCal c
INNER JOIN inserted i ON
i.Date = c.cadate AND
c.UID = i.UID
GO --end of trigger
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply