May 11, 2009 at 8:44 am
Hello,
I need to disallow a set of values from being inserted into a table.
I am trying to set up a trigger that allows insert and update as long as that set of values are not present
In the insert/update statement.
So the following is the logic for the trigger:
create TRIGGER check_null
ON
FOR INSERT, UPDATE
AS
BEGIN
/* Get the range of level for this job type from the jobs table. */
DECLARE @name varchar(100),
@email varchar(100)
SELECT @name = [name],
@email =
FROM inserted i
IF (@name <> 'forbidden name') and (@email <> NULL)
BEGIN
INSERT INTO [BuyRefi].[dbo].[mls Member]
([name],
)
SELECT
[name],
FROM inserted i
END
ELSE
BEGIN
SKIP --- This is where the problem is
END
END
I get the following error when I try to execute it : Incorrect syntax near 'SKIP'.
How can I skip a row insertion?
Many thanks in advance.
May 11, 2009 at 9:14 am
Why don't you simply use CHECK constraints?
Another way could be using INSTEAD OF triggers and insert manually only the rows that pass your test.
-- Gianluca Sartori
May 11, 2009 at 9:44 am
thank you for your prompt reply.
I did not try it because the table is being used by DTS packages I get errors whenever I use the alter table command. Plus I am a noob when it comes to sql server. I will google it further but is there a way to skip a row during insertion? I am trying to learn how to do that.
May 11, 2009 at 9:50 am
Try with INSTEAD OF triggers.
When a table has this kind of trigger, data is not inserted by the statement directly, but you have to insert data from the INSERTED logic table to the real table. You could easilly do it only for the rows that pass your check.
Look in BOL for INSTEAD OF triggers.
-- Gianluca Sartori
May 11, 2009 at 9:57 am
Hi, do not forget a trigger can be sent many rows at once, so your select with variables will not work.
Use this kind of approach to solve your problem, only the rows that fit will be inserted in the table you want.
You could also use a check constraint on the fields you want to be not null.
Here's some piece of code:
CREATE TRIGGER check_null
ON
FOR INSERT,UPDATE
AS
BEGIN
/* Get the range of level for this job type from the jobs table. */
INSERT INTO [BuyRefi].[dbo].[mls Member]
([name],
)
SELECT [name],
FROM inserted i
WHERE name 'forbidden name'
AND Email IS NOT NULL
END
Cheers,
J-F
May 11, 2009 at 10:03 am
Thanks, I'll look into it.
May 11, 2009 at 10:05 am
J-F's solution will work if you want to insert into a different table and not into the table on which the trigger is created. I don't understand if you're falling into the first or second case, maybe I misinterpreted the word "SKIP".
I hope you understand what I mean in spite of my poor English.
Regards
Gianluca
-- Gianluca Sartori
May 11, 2009 at 10:14 am
While you can do this, there is some question as to whether you should do it. The issue is that ofr most transactional methods, charges are "All Or Nothing". Which means if any inserted or updated record is invalid then all changes are rejected. If you use check constraints, then this is how they will work.
If you are really sure that you want to filter changes instead of rejecting the transaction, then either After Triggers (which is what you posted) or Instead Of Triggers will work. Because you can only have one set of Instead Of triggers, it is usually preferred to use After Triggers (so that triggers for other purposs can be added later, without having to modify yours).
Finally, the trigger example that you posted is written as though it is handling only a single row change, however, on SQL server triggers are fired once for each modifying statement, and not once for each modified row.
Here is how I would write these triggers:
create TRIGGER check_null ON
FOR INSERT AS
/* Since INSERTs and UPDATEs have to be handled differently
I have seprated them. */
BEGIN
-- Delete any inserted rows that are invalid:
DELETE From
Join inserted ON
.PrimaryKey = inserted.PrimaryKey
Where inserted.name = 'forbidden name'
OR inserted.email IS NULL
END
create TRIGGER check_null ON
FOR UPDATE AS
/* Since INSERTs and UPDATEs have to be handled differently
I have seprated them. */
BEGIN
-- Reset any updated rows that are invalid:
UPDATE
Set [name] = deleted.name
, email = deleted.email
-- might need to reset all of the other columsn too?
From
Join deleted ON
.PrimaryKey = deleted.PrimaryKey
Where deleted.name = 'forbidden name'
OR deleted.email IS NULL
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply