Trigger output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger output

Author

Topic

TestEngineer
Starting Member

29 Posts

Posted - 2006-01-10 : 15:14:40

I need to use a GUID as the primary keys for tables I was planning on using an Identity for. The tables are to be synchronized between facilities, so if someone updates a table at Facility B, then someone updates a table at Facility A, then the records are synchronized, if Facility A is the master database, the record from Facility B will be lost. Since I don't want this, I'm modifying the tables to use the GUID.

I have some stored procedures that I've created that utilize Scope_Identity and found a method I'd like to use to substitute for this. It is:

CREATE TRIGGER trigBoardsGUID
ON [EngineeringUser].[Boards]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
SELECT Board_GID AS '@@GUID'
FROM inserted
END

The question is this: How do I get this uniqueidentifier, @@GUID, into my stored procedure(s)? I tried:
DECLARE @BoardGUID uniqueidentifier
SET @BoardGUID = (INSERT INTO Boards (Part_ID) VALUES (1))
but on syntax check, I get an error near 'INSERT' and an error near ')'

I'd also like to know how to similarly return other stored procedure OUTPUT parameters into a stored procedure.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-10 : 16:08:09

since guids are unique, you don't need to use a trigger to emulate the identity functionality. You can simply create a guid prior to the insert and include it with the insert.

Here is an example that illustrates that as well as your OUTPUT question:


use pubs
set nocount on
go

create proc SomeTable_ins

@columns int
,@BoardGUID uniqueidentifier = null output

as

set @BoardGUID = newid()

--insert sometable (BoardGUID)
--values (@BoardGUID)

go

--call SP and retrieve the output variable value
declare @guid uniqueidentifier
exec SomeTable_ins @columns = 1, @BoardGUID = @guid output

select @guid [@guid]

go
drop proc SomeTable_ins

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-10 : 16:17:48

btw, Aren't there strategies out there to co-locate data with identity columns? I've never done it but it could be worth the time to look into it because I believe guids are not the ideal PK type for optimization reasons.

Be One with the Optimizer
TG

TestEngineer
Starting Member

29 Posts

Posted - 2006-01-10 : 16:43:02

Thanks TG for the quick reply to this post.

I may end up using your method, but would really like to allow the automatic creation of the GUID by the table (less code rewrite on my part).

-- Begin edit addition
I have instances of direct inserts into the tables from my VB6 code and others using stored procedures. If I utilize the trigger method, the cases in my code that do not reference the ID will require no change, while if I use a stored procedure or opt to manually insert the GUID, then I'll have to rewrite all the code.
-- End edit addition

I talked with the DBA for our company and he said that I'd lose records created on the remote database if a record with the same primary key value is added between synchronizations on the primary database. If there is a way (through triggers maybe?) to update identity fields on tables being synchronized with a new value to prevent destruction, I'd much rather prefer to do that.

Thanks again in advance for anyone's help working this out.

Tom

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-11 : 11:13:30

Just make sure that you understand the downside2 of using a GUID.
1. It takes more storage, 16 bytes instead of 4 for an integer, and even more space if it is in an index.
2. A uniqueidentifier datatype is not usually a good candidate for a clustered index, because new rows will be inserted in random locations throughout the table and cause page splits.

If you want to automatically generate the uniqueidentifier, create a default constraint on the column with a default value of NEWID().

As TG mentioned, there are other ways to handle IDENTITY couumns in a replicated enfironment. You should at least make sure you understand the pros/cons of all alternatives before you convert to uniqueidentifier. Consult Books Online. Google is your friend. I don't know if anyone here can point you at articles on the subject.

One thing you could consider doing is using BIGINT identity columns if you have a relatively small number of sites to combine. You could reserve a range of identities for each site, and enforce it with a check constraint on the IDENTITY column that is different for each site. For example, Site 1 gets 1,000,000,000,000 through 1,999,999,999,999, Site 2 gets 2,000,000,000,000 through 2,999,999,999,999, and so on. Then you know that each identity value will be unique across all sites, and each table will have a one trillion number range to work with.

CODO ERGO SUM

TestEngineer
Starting Member

29 Posts

Posted - 2006-01-11 : 15:46:18

Thanks for the insight Michael.

I'm beginning to read more about the replication process from books online. We have two facilities in Ohio, one in Tennessee, two in Juarez, and a distribution facility in Texas. There will likely only be 4 facilities using the databases initially, but we are a growing company.

When the replication is setup, I assume I can change the constraints at each location and they will stay that way. Does the replication automatically enable identity insert? Do the constraints on the database table keep the identity from incrementing from the highest number? For example, Facility A has a record 1, Facility B has a record 1,000,000,001. After synchronization, will Facility A's next record still be 2?

Thanks again for the help.

TestEngineer
Starting Member

29 Posts

Posted - 2006-01-12 : 09:32:58

I found the information I was looking for. You can have SQL Server 2000 automatically manage the identity ranges used on the remote database tables (subscribers) and the "master" database table (publisher) by assigning a range and threshold value to the first subscriber and to the publisher. After the threshold is reached in one location, the SQL Server will automatically create a new range for the table in that location on the next sync.

This means I can set my range much smaller than 100 billion for each facility. I will set the range based on the usage and sync schedule our IS department sets up. Whew..

Now I have one last question: I've been able to change the integer data type of a table that is not replicated (from tinyint to smallint, smallint to int, etc.). Can this be done on a set of tables that is replicated? If you change the datatype in the "publlisher" table, will the subscriber tables datatypes change on sync?

Thanks again for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources