Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQLServer Triggers

Posted on 1999-08-17
4
Medium Priority
?
1,129 Views
Last Modified: 2008-03-06
The MS Sqlserver trigger shown below is currently the method we use to update the RECNUM field of the CLIENT file with an incrementing value.
Any suggestions on a better way of doing it, that uses less resources would be greatfully accepted.

In particular, I am wondering if the Client field RECNUM can be directly updated by directly referencing the field rather than having to do an update statement.  Most other database products we have used can directly update a field in a trigger.

Ie: In Oracle you can do this;    :NEW.RECNUM = 1

CREATE TRIGGER Trg_Client ON CLIENT FOR INSERT AS
DECLARE
     @NEW_RECNUM INT
SELECT @NEW_RECNUM = ISNULL(MAX(RECNUM), 0) + 1 FROM CLIENT
UPDATE CLIENT SET RECNUM = @NEW_RECNUM WHERE RECNUM = 0"

0
Comment
Question by:finster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Expert Comment

by:tschill120198
ID: 1097573
Sounds like an identity column would work... something like:

    create table Client (
        RecNum      int          identity,
        ClientName  varchar(30)  not null
    )
    go
    insert Client (ClientName) values ('ClientA')
    insert Client (ClientName) values ('ClientB')
    insert Client (ClientName) values ('ClientC')
    go
    select * from Client
    go
Should give something like:
    RecNum   ClientName
    -------- -------------
    1        ClientA
    2        ClientB
    3        ClientC

Look up 'identity' in Books Online for more details...

Is that what you are looking for?
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 1097574
Also be aware that only one trigger fires for an insert, so even if you insert a load of rows by doing and insert into, only one trigger will fire.
So in your example above all the new rows would be set to the same recnum.
0
 
LVL 3

Accepted Solution

by:
gmoriak earned 400 total points
ID: 1097575
You can use the identity property and SQL will automatically supply a unique value for you.

Other than that you have to do what you're doing
However:  there is a table called updated which has each of the rows that were updated.  You can refer to it in your trigger.

create trigger trg_client on client for insert
as
begin
      update c
      set    c.recnum = (select isnull(max(c2.recnum), 0) + 1 from cust c2)
      from   client c, inserted u
      where  u.recnum = c.recnum
end




0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6871482
GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question