Triggers

Question

Post reply

Triggers

Ron McCullough

SSC Guru

Points: 63877
More actions
February 23, 2010 at 9:22 pm

#230299

Comments posted to this topic are about the item Triggers
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
February 23, 2010 at 10:25 pm

This was removed by the editor as SPAM
February 24, 2010 at 2:35 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

JayK SSCrazy Points: 2679 More actions · Answer 1

The answer to this question should be NO.

The SQL as written will generate an error:

Msg 111, Level 15, State 1, Line 2

'CREATE TRIGGER' must be the first statement in a query batch.

If you take out the USE statement is works OK.

Mighty SSCrazy Eights Points: 9063 More actions · Answer 2

stewartc-708166 (2/23/2010)
point of note:
Schema definitions are case sensitive.
therefore the schema of the table given as dbo, while the schema of the trigger is Dbo.
removing the trigger schema or making it the same case as the table/view definition will make it work.
otherwise the error will be:
Cannot create trigger 'Dbo.TvDiscontinuedProducts' because its schema is different from the schema of the target table or view.

What you say is not (completely) correct. What is true, is that the trigger and the table/view need to belong to the same schema.

When your database collation is case sensitive, then of course dbo and Dbo are two different schemas and then this error will occur.

Mighty SSCrazy Eights Points: 9063 More actions · Answer 3

jack.kennedy (2/24/2010)
The answer to this question should be NO.
The SQL as written will generate an error:
Msg 111, Level 15, State 1, Line 2
'CREATE TRIGGER' must be the first statement in a query batch.
If you take out the USE statement is works OK.

Not sure what you do incorrect (forgot the GO), but even the code below works.

USE OPLIB

GO

CREATE TRIGGER DBO.TvDiscontinuedProducts ON dbo.vw_borrower

instead of insert AS Print 'inserts not authorized'

GO

USE OPLIB

GO

DROP TRIGGER DBO.TvDiscontinuedProducts

GO

Otherwise it would be quite impossible to make SQL batch file containing the creations of trigger otherwise.

Answer YES is the correct one.

JayK SSCrazy Points: 2679 More actions · Answer 4

Ah - what a dummy I am - Had the GO on the same line - that'll teach me to be so quick to criticize. I'll have to wait 24 hrs to redeem myself 😉

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 5

stewartc-708166 (2/24/2010)
Should have gone with my gut feel and not tested to confirm

I keep saying this to my lead developer. He doesn't really listen to me anymore, I'm not sure why. 😉

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 6

At first I was disturbed by the lack of a rollback, then realized it was an istead of trigger.

I'm going to use that in my robotic arsenal, thanks BB!

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Thanks Ron. Good question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

john.moreno Default port Points: 1485 More actions · Answer 8

I'd say the right answer is: it depends.

"INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION."

So, we don't really have enough information to say whether a INSTEAD OF trigger can created on dbo.vDiscontinuedProducts.

john.arnott SSChampion Points: 11882 More actions · Answer 9

Nice tool to know of. BTW, although the QOD starts off saying "in SQL 2008...", the INSTEAD OF option is documented for SQL 2005 as well.

OCTom SSChampion Points: 11785 More actions · Answer 10

Good question. Being fairly new to SQL Server, I would not have thought that views could have triggers. It seems counter-intuitive to me. I learned something new, AGAIN!! :w00t:

Del Lee SSCrazy Points: 2781 More actions · Answer 11

The name of the trigger and the view differed only by a single letter, and I missed that. I thought they were named the same and was thus a trick question.

Bummer.

Del Lee

Paul White SSC Guru Points: 150467 More actions · Answer 12

A PRINT statement in a trigger :sick:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi