Post reply

[newbie]

  • January 21, 2009 at 9:25 am

    #130311

    I'm trying to create a trigger that retrieves the max value of a primary key column and updates max + 1 to the primary key itself. This triggers off whenever there is an INSERT update on the [task] table in my case.

    How do I assign the value of the SELECT statement to a variable?

    CREATE TRIGGER tu_taskPK on [teamwiki].[dbo].[task]

    FOR INSERT AS EXEC GenerateTaskPK

    GO

    IF OBJECT_ID('generateTaskPK') IS NOT NULL

    DROP PROCEDURE generateTaskPK

    GO

    CREATE PROCEDURE [generateTaskPK]

    @maxTaskID AS bigint OUTPUT

    AS

    SELECT MAX([taskID_PK]) //taskID_PK is a bigint

    FROM [teamwiki].[dbo].[task];

    GO

    @maxTaskID = value of above query ??

    UPDATE [teamwiki].[dbo].[task]

    SET taskID_PK = @maxTaskID

    WHERE taskID_PK = '0';

    2. Within SQL Server Management Studio (for SQL Server Express 2005), I could not visually view the (incorrect) trigger / procedures I created under the Database Triggers / Programmability > Stored Procedures tabs respectively.

    Any idea how to view/delete the existing trigger from SQL Server Mgmt Studio?

  • January 21, 2009 at 9:28 am

    #930466

    First, why use a trigger instead of an Identity column?

    Second, you assign a value to a variable by:

    declare @MyVar datatype

    select @MyVar = column

    from table

    Third, you need to look at the triggers tab under the specific table. Database triggers are triggers at the database level, which isn't what you're looking at here. Click on the database name, expand Tables, expand the table you want, expand Triggers.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 21, 2009 at 9:56 am

    #930491

    I'd use identity column as G noted.

    But you also must keep in mind that all triggers MUST be written to accept more than 1 row at a time.

    Your trigger will not do what you expect if you insert multiple rows

    for example

    Insert into TeamWiki (...)

    Select ...

    From sometable

    and to view /edit/ delete trigger from table in ssms.

    IN Object explorer connect to server, DB, Table. Expand triggers folder, and it should be listed there

  • January 21, 2009 at 11:23 am

    #930575

    What it'll do with multiple rows is nothing compared to what it'll do if there are multiple simultaneous inserts. That's one of the reasons I recommend against using a trigger for that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 21, 2009 at 11:57 am

    #930598

    Thanks. In my case I'm not worried about multiple inserts at the moment, but it's a good issue.

    At the moment the database has to be updateable, so I'll have to test how the locks are working. Since I left the default installation, would you know how this is done, and, point me to some online reference if this is available?

  • January 21, 2009 at 12:51 pm

    #930638

    Would We know how What is done?

    I might be missing something, but what question are you asking specifically.

    are you asking about locking?

  • January 21, 2009 at 12:56 pm

    #930647

    Yes. What is the default locking behaviour for SQL Server 2005 Express Edition (i.e. optimistic, pessimistic, opportunistic)?

    How do I change it? Any links to some technical references?

  • January 21, 2009 at 2:31 pm

    #930723

    What is incorrect with the syntax of this statement?

    ALTER TABLE [application_db].[dbo].[task]

    ALTER COLUMN taskID numeric identity (2,0);

    :w00t:

    Error:

    Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'identity'.

    Related links:

    http://technet.microsoft.com/en-us/library/ms190273.aspx - ALTER TABLE reference

    http://www.sqlteam.com/article/understanding-identity-columns - Identity columns tutorial

  • January 21, 2009 at 3:38 pm

    #930761

    You cannot use ALTER TABLE to change the identity attribute of a column. Use Management Studio to do it.. it will magically recreate the table for this.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • January 22, 2009 at 10:59 am

    #931319

    Thanks. As a matter of fact I had to re-create the table from scratch. Any idea if this feature is available in Server 2008?

  • January 22, 2009 at 12:53 pm

    #931408

    Not sure if it is allowed in 2008, I'm thinking not.

    But as described above when modifying the Identity property of a column in SSMS, all it really does behind the scenes is create a new table using the same DDL (except adding the Identity info) Copies the data from existing table to the new table, Drops the old table and renames new table to old name.

    so if you have a bunch of tables you need to this to, that is your only option (If your not going to use SSMS of course)

  • January 23, 2009 at 8:07 pm

    #932144

    Jon (1/22/2009)

    Thanks. As a matter of fact I had to re-create the table from scratch. Any idea if this feature is available in Server 2008?

    Nope, sorry.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • January 24, 2009 at 1:04 pm

    #932274

    Jon (1/22/2009)

    Thanks. As a matter of fact I had to re-create the table from scratch. Any idea if this feature is available in Server 2008?

    You're not listening.... several people have tried to tell you to use the IDENTITY property of an Integer column instead of shooting yourself in the head with a RBAR trigger and no worrying about concurrancy, etc, etc. The method you propose is computational suicide. Look up IDENTITY and OUTPUT in Books Online.

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

  • January 25, 2009 at 12:56 am

    #932336

    The IDENTITY column worked fine, I asked for information. Thanks for the advice.

  • January 25, 2009 at 9:07 am

    #932373

    Whew! Thanks for the feedback, Jon... thought you were falling on a sword and didn't want to see that happen. 🙂

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

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

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