Solved

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

Posted on 2003-11-03
8
1,186 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:johan777
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Expert Comment

by:russellshome
ID: 9669965
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
 

Author Comment

by:johan777
ID: 9670002
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
 
LVL 5

Expert Comment

by:russellshome
ID: 9670092
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9670469
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:johan777
ID: 9671245
Thanx Namasi!!!

Excellent response!!!
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 9673494
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
 

Author Comment

by:johan777
ID: 9676779
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9678725
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now