February 2, 2011 at 9:12 am
I am thinking about the possibility of implementing some sort of trigger or other constraint on my data schema that will throw warnings on insert or update instead of full blown errors with rollback. For my situation, I am dealing with brands and product codes. Two different brands could have the same product code, but it is very unlikely. Because of this, I would like to allow these to exist, but warn the user when the event occurs.
1. Is this a bad design that can be done in a better way?
2. How can I throw a warning as opposed to an error, is it possible?
3. Do any of you use a similar technique?
Thank you!
February 2, 2011 at 11:43 am
I know for sure Triggers are the burden for sure. Unless its necessary to have them in your table don't use them. But anyway it depends on the environment, transactions flow and so on... I just wrote two triggers one for insert and another for update to process to continue.
I prepared only concerning the trigger.. It may be useful
Try the following:
Create Trigger [dbo].[WARNING_updated]
ON YourTableName
for update AS
If ( Update(Brand) or Update(Product))
print'WARNING:'
Print 'Other Brand also the has the same productID'
----------------------------------------------------
Create trigger [dbo].[Warning_inserted]
on YourTableName
for insert as
If exists (select 1 from inserted )
print'WARNING:'
Print 'Other Brand also has the same productID'
Please change the warning message accordingly and the logic inside.
I don't know the other way to write both in one trigger. If something wrong please correct me.
February 2, 2011 at 5:15 pm
Actually these are great. That is the behavior I'm looking for. Although I am going to continue looking for a way to avoid using triggers for this. But, I wonder how I can design a table against data that 'for the most part' shouldn't be there without triggers.
I'm thinking of possibly having some sort of 'authorized duplicates' table, where, if a batch of products are inserted into the main table, they will fail unless they are first authorized. In this way, if there are *invalid* duplicates, the only way they could be inserted is after they were authorized, which would be purely negligent at that point.
Thanks douknowthesolution for the triggers!
February 2, 2011 at 6:55 pm
Do not know if you are inserting one row at a time, or as a batch, that said you may want to examine using within your update/insert SP, a
IF EXISTS statement so as not to input "duplicates"
IF EXISTS (SELECT * FROM your table WHERE yourcolumn = ...
or use a variant as IF NOT EXISTS --
Either way would prevent unwanted items being entered, and all the code to do that is in your SP, not in a trigger.
February 3, 2011 at 11:28 am
I would say if you are worried about something with 2 brands having the same product code then it is a bad design; as you say this is allowable then why need a warning? It is a good record. PK should consist of both product code and brand if using referential integrity as unique.
February 3, 2011 at 11:48 am
I would code the front end to check for duplicates and warn the user.
You won't get anything useful from a trigger unless you have a "known duplicate" column in the table and when the user adds a product that column is null.
The trigger checks for a duplicate and if it finds one and the "known duplicate" column on the insert is null then it rolls back the transaction with a nice error message for the application.
The user gets the warning and ticks a box to say "yes I know it is a duplicate but go ahead" and submits again - this time the trigger allows it because the "known duplicate" column is populated.
But I would stick to having that logic all in the application myself.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 4, 2011 at 7:55 am
mister.magoo (2/3/2011)
I would code the front end to check for duplicates and warn the user....
But I would stick to having that logic all in the application myself.
I agree with mister.maggo here. This kind of logic belongs in the application code, not the database, especially not in a trigger. If you are using stored procedures, then so the If Exists check like Ron (bitbucket) suggests, if you are not using stored procedures then I'd suggest adding the if check before the insert or update. It may be an extra roundtrip, but it should be quick, lightweight.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply