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?
 
Scott PletcherConnect With a Mentor Senior 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
 
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.