finster
asked on
SQLServer Triggers
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"
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"
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.
So in your example above all the new rows would be set to the same recnum.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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?