How do I use Newid() inside a INSERT TRIGGER?

Hi,

MS suggests that one should set the NEWID() function as a Default on the column. This method, however, allows one to populate one's own value, effectively overriding the default supplied.

I come from a Interbase background where one has a BEFORE and AFTER TRIGGER entry point.  From a security perspective it is then easy to prevent anyone from trying to circumvent the database integrity and populate their own GUID data. All one has to do is to populate the GUID inside the AFTER TRIGGER on the INSERT entry point of the table and then to check that the GUID column does not change in the BEFORE TRIGGER on the UPDATE entry point.

My understanding is that the MS equivalent method would be to populate the NEWID() in the standard INSERT TRIGGER and then do the check inside the AFTER UPDATE TRIGGER.

I would like to know: 1) Is this solution the correct approach?
                               2) How do I get the NEWID() to supply a unique value for each row to be inserted? Remember that triggers are set based and not row based.

Thanx
Johan Swart
johan777Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

russellshomeCommented:
I would keep the newid() as default as suggested by MS
Plus have a trigger for update that says if the inserted guid is different to the deleted guid then raise an error and rollback the transaction
0
johan777Author Commented:
Hi Russel,

Thanx for the comment.

I like the trigger for update part of your answer. Thanx.

The default would not work though, as one can then still supply one's own values during the insert, therefore bypassing the db integrity.

Regards,
Js
0
russellshomeCommented:
Ensure the only access to the table is via stored procedures & views
I.e. Create a user that your application or users utilise when connecting. Give this user permission only to an insert proc that inserts the appropriate way.

You should do this anyway and it is the easiest method for a number of similar problems.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

namasi_navaretnamCommented:
1) In the insert trigger do the following. Only thing you need to be concenred is that inserted table will have more than one for batch insert (a sql that inserted more than one row at a time). In that case you need to loop thru and set new_id or raiseeror

2) In the update trigger join  inserted table with actual table by new_id . Here also you need to be concerned about batch update.

select @rowcount = count(*)
from inserted i, table t
where t.newid = t.newid

If (@rowcount <> select count(*) from inserted) then raiseerror


CREATE TRIGGER ti_Table
ON Table FOR INSERT
AS
BEGIN

If Exists (SELECT 1 FROM inserted)
BEGIN
     
    DECLARE table_cursor CURSOR FOR
    select  col1, col2, etc
    from inserted

    OPEN table_cursor

    FETCH NEXT FROM table_cursor
    INTO @vCol1, @Col2, @Etc

    WHILE @@FETCH_STATUS = 0
    BEGIN
 
       // Get New Id here and set it
       // Or if the supplied value is not NewId raiserroe      
   
       FETCH NEXT FROM table_cursor
       INTO @vCol1, @Col2, @Etc

     END

     CLOSE TABLE_CURSOR
     DEALLOCATE TABLE_CURSOR

END


END


HTH

Namasi
0
johan777Author Commented:
Thanx Namasi!!!

Excellent response!!!
0
Scott PletcherSenior DBACommented:
SQL does not have BEFORE triggers, instead:

SQL Server 7.0   :  AFTER triggers only
SQL Server 2000:  AFTER or INSTEAD OF triggers

I think the best solution is an INSTEAD OF trigger if available, since it is the least overhead (you avoid having to do an UPDATE after the INSERT), including allowing you to avoid a cursor.  For example:


CREATE TRIGGER yourTriggerNameHere
ON yourTableNameHere
INSTEAD OF INSERT
AS
INSERT INTO yourTableNameHere (col1, col2, col3, uniqidCol)
SELECT col1, col2, col3, NEWID()
FROM inserted
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johan777Author Commented:
Hi Scott,

Thanx for the solution. I Agree, the INSTEAD OF TRIGGER looks to be the cheapest while still enforcing the integrity on the db.

If I have multiple triggers on the insert, will the INSTEAD OF TRIGGER execute first, last or is it not possible to say when in sequence it will execute?

On the UPDATE TRIGGER portion of my statement should the IF UPDATE(col1) statement not be the cheapest option?

Regards,
Js
0
Scott PletcherSenior DBACommented:
If you use an INSTEAD OF trigger, you will not be able to use an AFTER INSERT trigger also.  And it really isn't needed.  Just do whatever you need to do in the INSTEAD OF trigger after doing the INSERT.

Yes, in the AFTER UPDATE trigger, you're right, it would be a good idea to check for UPDATE(col1).  If that column was not affected, you can exit the UPDATE trigger immediately.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.