Trigger Execution Problem

Question

Post reply

Trigger Execution Problem

shohelr2003

Hall of Fame

Points: 3535
More actions
December 29, 2014 at 4:59 am

#302058

Dear,
I have created an after insert trigger on a table. When I click on Save button from a particular client machine it was supposed to throw "You can not issue" message. But the message appears "The definition of object 'triggerName' has changed since it was compiled". I have used the following codes.
DECLARE @IP VARCHAR(15);
select @IP = client_net_address
from sys.dm_exec_connections
where session_id=@@SPID
IF (@IP ='127.0.0.1' )
BEGIN
RAISERROR('You can not issue',16,1);
ROLLBACK
END
I am not getting the issue. Please help me to get out from the loop.
Thanks
Akbar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Dear Akbar,

have you tried disabling and re-enabling the trigger?

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Shafat Husain SSCrazy Points: 2754 More actions · Answer 2

Dear Akbar,

If it didn't solve your issue share the complete code and version of your SQL.

Also have you recently upgraded your SQL Version.

Saw a similar kind of thread on msdn who was having issue after upgrade.

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 3

Here is my full trigger. Please help me.

CREATE TRIGGER [dbo].[myTrigger]

ON [dbo].[myTable]

AFTER INSERT

AS

BEGIN

DECLARE @IP VARCHAR(15);

select @IP = client_net_address

from sys.dm_exec_connections

where session_id=@@SPID

IF (@IP = '127.0.0.1' )

BEGIN

RAISERROR('You can not issue',16,1);

ROLLBACK

END

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Truly a shot in the dark but I've seen worse. It could be that you need to clear cache on the desktop that you're running the code from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 5

Quick suggestion, should be enough to get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.myTable') IS NOT NULL DROP TABLE dbo.myTable;

GO

CREATE TABLE dbo.myTable

(

MT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,MT_VAL INT NOT NULL

,MD_DT DATETIME NOT NULL DEFAULT (GETDATE())

);

GO

CREATE TRIGGER [dbo].[myTrigger]

ON [dbo].[myTable]

FOR INSERT

AS

BEGIN

IF (SELECT COUNT(*) FROM sys.dm_exec_connections WHERE session_id = @@SPID AND client_net_address IN ('127.0.0.1','<local machine>') ) > 0

BEGIN

RAISERROR('You can not issue',16,1);

ROLLBACK

END

GO

INSERT INTO dbo.myTable (MT_VAL) VALUES (1),(2),(3);

GO

SELECT

MT.MT_ID

,MT.MT_VAL

,MT.MD_DT

FROM dbo.myTable MT;