After Insert trigger

Question

Post reply

After Insert trigger

Arsh

SSCertifiable

Points: 6354
More actions
June 25, 2018 at 1:27 am

#413962

Hi All,
I'm trying to create an After Insert in SQL Server 2008 R2 with the code below. The user I'm logging in has db_datareader and db_datawriter roles assigned but for some reason the Tables folder shows no objects but sysobjects shows the object is there. Below is the code I'm using :

CREATE TRIGGER TrggrName
ON [TableName]
AFTER INSERT AS
BEGIN
Exec [dbo].[StoreProcedure]
END
GO
I get this error below
Msg 8197, Level 16, State 4, Procedure TrggrName, Line 1
The object 'TableName' does not exist or is invalid for this operation.
Any help is appreciable.
Thank You
Arsh

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What do you get if you run this?

SELECT entity_name , subentity_name , permission_name FROM fn_my_permissions ( TableName , 'OBJECT' )

John

Arsh SSCertifiable Points: 6354 More actions · Answer 2

John Mitchell-245523 - Monday, June 25, 2018 2:07 AM
What do you get if you run this?
SELECT entity_name , subentity_name , permission_name FROM fn_my_permissions ( TableName , 'OBJECT' )
John

Hi John ,
Thanks for the reply. The query returns nothings. Also its a 'intellisense' issue for which I m installing V Studio . |tried alternatively from command prompt but bcos I've only the db_datareader and db_datawriter roles , I get permissions error. Will fix these and let know. Can drop any comments meanwhile.

Thanks...Arshad

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Arsh - Monday, June 25, 2018 1:27 AM
Hi All,
I'm trying to create an After Insert in SQL Server 2008 R2 with the code below. The user I'm logging in has db_datareader and db_datawriter roles assigned but for some reason the Tables folder shows no objects but sysobjects shows the object is there. Below is the code I'm using :

CREATE TRIGGER TrggrName
ON [TableName]
AFTER INSERT AS
BEGIN
Exec [dbo].[StoreProcedure]
END
GO
I get this error below
Msg 8197, Level 16, State 4, Procedure TrggrName, Line 1
The object 'TableName' does not exist or is invalid for this operation.
Any help is appreciable.
Thank You
Arsh

Is "TableName" the actual name of the table you're trying to attach the trigger to?

--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)

Arsh SSCertifiable Points: 6354 More actions · Answer 4

Jeff Moden - Monday, June 25, 2018 6:24 AM
Arsh - Monday, June 25, 2018 1:27 AM
Hi All,
I'm trying to create an After Insert in SQL Server 2008 R2 with the code below. The user I'm logging in has db_datareader and db_datawriter roles assigned but for some reason the Tables folder shows no objects but sysobjects shows the object is there. Below is the code I'm using :

CREATE TRIGGER TrggrName
ON [TableName]
AFTER INSERT AS
BEGIN
Exec [dbo].[StoreProcedure]
END
GO
I get this error below
Msg 8197, Level 16, State 4, Procedure TrggrName, Line 1
The object 'TableName' does not exist or is invalid for this operation.
Any help is appreciable.
Thank You
Arsh
Is "TableName" the actual name of the table you're trying to attach the trigger to?

Hi Jeff and John. Thanks for the advices . The issue was with Intellisense. Setting up SQL server 2017 resolved the issue.

Best Regards,

Arshad

Arsh SSCertifiable Points: 6354 More actions · Answer 5

Arsh - Sunday, July 1, 2018 12:01 AM
Jeff Moden - Monday, June 25, 2018 6:24 AM
Arsh - Monday, June 25, 2018 1:27 AM
Hi All,
I'm trying to create an After Insert in SQL Server 2008 R2 with the code below. The user I'm logging in has db_datareader and db_datawriter roles assigned but for some reason the Tables folder shows no objects but sysobjects shows the object is there. Below is the code I'm using :

CREATE TRIGGER TrggrName
ON [TableName]
AFTER INSERT AS
BEGIN
Exec [dbo].[StoreProcedure]
END
GO
I get this error below
Msg 8197, Level 16, State 4, Procedure TrggrName, Line 1
The object 'TableName' does not exist or is invalid for this operation.
Any help is appreciable.
Thank You
Arsh
Is "TableName" the actual name of the table you're trying to attach the trigger to?
Hi Jeff and John. Thanks for the advices . The issue was with Intellisense. Setting up SQL server 2017 resolved the issue.
Best Regards,
Arshad

I mean installing the SQL Client 2017 resolved the issue.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

Arsh - Tuesday, July 3, 2018 2:19 AM
Arsh - Sunday, July 1, 2018 12:01 AM
Jeff Moden - Monday, June 25, 2018 6:24 AM
Arsh - Monday, June 25, 2018 1:27 AM
Hi All,
I'm trying to create an After Insert in SQL Server 2008 R2 with the code below. The user I'm logging in has db_datareader and db_datawriter roles assigned but for some reason the Tables folder shows no objects but sysobjects shows the object is there. Below is the code I'm using :

CREATE TRIGGER TrggrName
ON [TableName]
AFTER INSERT AS
BEGIN
Exec [dbo].[StoreProcedure]
END
GO
I get this error below
Msg 8197, Level 16, State 4, Procedure TrggrName, Line 1
The object 'TableName' does not exist or is invalid for this operation.
Any help is appreciable.
Thank You
Arsh
Is "TableName" the actual name of the table you're trying to attach the trigger to?
Hi Jeff and John. Thanks for the advices . The issue was with Intellisense. Setting up SQL server 2017 resolved the issue.
Best Regards,
Arshad
I mean installing the SQL Client 2017 resolved the issue.

K... but I'm confused. Your trigger code was creating a trigger on a table named "TableName" and you got an error saying that table didn't exist. I'd bet that table name still doesn't exist. How did installing the SQL Client 2017 fix THAT problem?

--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)