johan777
asked on
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
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
ASKER
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
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
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.
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.
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
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
ASKER
Thanx Namasi!!!
Excellent response!!!
Excellent response!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
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