[dba-SQLServer] Trigger syntax

Gustav,
  First disable the intellisense in ssms
http://sqlserver.ro/blogs/cristians_blog/archive/2007/11/20/turning-off-intellisense-in-sql-server-2008-ctp5.aspx

  Go to the redgate site and checkout sql prompt

http://www.red-gate.com/products/sql-development/sql-prompt/


try the trialware for the 14 days (you can auto-extend the trial once
expired by typing in I need more time and it gives you another 14 days).

I think you'll like sql prompt, it's a real working intellisense, and just
like in .net it works really well

-Francisco
http://bit.ly/sqlthis   | Tsql and More...
Let Apple know you want turn by turn on your iPhone 4!
https://plus.google.com/112803888444646122406/posts/HmQej9pHZQM

 <http://db.tt/JeXURAx>




On Thu, Jun 14, 2012 at 3:17 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi Mark
>
> No, I use SSMS.
>
> /gustav
>
>
> >>> marklbreen at gmail.com 14-06-12 11:30 >>>
> Hello Gustav,
>
> Oh yes, the intellisense can cause a lot of hassle chasing a syntax error
> that does not exist.
>
> Do you use SQL Prompt by Redgate?
>
> Mark
>
>
> On 12 June 2012 09:39, Gustav Brock <gustav at cactus.dk> wrote:
>
> > Hi Jim
> >
> > Yes, nothing fancy. And besides, the trigger works fine.
> >
> > I guess somehow the new trigger, though saved and working, was not
> > recognised by its name as a member of the collection by the intellisense
> > until a reboot had happened.
> >
> > /gustav
> >
> >
> > >>> accessd at shaw.ca 11-06-12 19:14 >>>
> > Hi Gustav:
> >
> > Have set up all the permissions right?
> >
> > Jim
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
> > Brock
> > Sent: Monday, June 11, 2012 4:10 AM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] Trigger syntax
> >
> > Hi Mark
> >
> > The code below was the code generated by the SQL template which is
> created
> > when you add a new trigger.
> > As you know, when you run the SQL containing "CREATE ...", the trigger
> > itself containing "ALTER ..." is created.
> > A trigger for Insert already existed. It is named
> > [dbo].[T_VariantsTable_ITrig].
> > That's why I chose the name [dbo].[T_VariantsTable_UTrig] for my Update
> > trigger.
> >
> > However, this morning - after my workstation had been closed down for the
> > weekend - the issue has vanished. So, for some reason, the new trigger
> > hadn't been saved in full - or what? I really did logout and login again
> > several times and refreshed "everything". But no reboot. Is that really
> > needed?
> >
> > /gustav
> >
> >
> > >>> marklbreen at gmail.com 11-06-12 10:33 >>>
> > Hello Gustav
> >
> > I just copied your code into SSMS, change the two table names and it
> > worked.  It created a trigger on a db here locally on my machine.
> >
> > The only thing I had to do was change the Alter to a Create.  Would that
> be
> > your problem?
> >
> > If not, the only other thought that comes to mind is the PPT database the
> > one with the table to hold the trigger.  IOW, does the db and table
> exist?
> >
> > Sorry I could not help more,
> >
> > Mark
> >
> >
> > On 8 June 2012 14:08, Gustav Brock <gustav at cactus.dk> wrote:
> >
> > > Hi all
> > >
> > > I'm new to triggers but I've managed to create this which works as
> > > intended:
> > >
> > > USE [PPT]
> > > GO
> > > /****** Object:  Trigger [dbo].[T_VariantsTable_UTrig]    Script Date:
> > > 06/08/2012 14:45:17 ******/
> > > SET ANSI_NULLS ON
> > > GO
> > > SET QUOTED_IDENTIFIER ON
> > > GO
> > > -- =============================================================
> > > -- Author:              Gustav Brock
> > > -- Create date: 2012-06-08
> > > -- Description: Update field Created to time of update of record
> > > -- =============================================================
> > > ALTER TRIGGER [dbo].[T_VariantsTable_UTrig]
> > >   ON [dbo].[VariantsTable]
> > >   FOR UPDATE
> > > AS
> > > BEGIN
> > >    -- SET NOCOUNT ON added to prevent extra result sets from
> > >    -- interfering with SELECT statements.
> > >    SET NOCOUNT ON;
> > >
> > >    -- Insert statements for trigger here
> > >    DECLARE @id INT
> > >    DECLARE @now DATETIME
> > >
> > >    -- Retrieve ID of changed record.
> > >    SELECT @id = (SELECT ID FROM deleted)
> > >    -- Get current time without milliseconds.
> > >    SELECT @now = DATEADD(SECOND, DATEDIFF(SECOND, '20000101',
> GETDATE()),
> > > '20000101')
> > >
> > >    UPDATE VariantsTable
> > >    SET Changed = @now
> > >    WHERE ID = @id
> > > END
> > >
> > > However, when I open this in SMMS, this line has the last part red
> > > underlined:
> > >
> > > ALTER TRIGGER [dbo].[T_VariantsTable_UTrig]
> > >
> > > stating: Invalid object name 'dbo.T_VariantsTable_UTrig'
> > >
> > > Why is that?
> > > I have another trigger created by the Access upsize wizard with similar
> > > code:
> > >
> > > ALTER TRIGGER [dbo].[T_VariantsTable_ITrig]
> > >
> > > and nothing is underlined here.
> > >
> > > /gustav
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>