Trigger blues...
-
So I have Table1, 2 columns K1 varchar(10), K2 BIT(defauilt 0). When I insert a record, I wish K2 to be set to 1, if a condition in K1 is met, like:
If LEFT(K1,1) = 'x' THEN K2 = 1
I've been playing around with inserted and deleted, updating the orgininal table, but I can't get this done. I could update the record AFTER insertion, but I would like to change K2 during the insert.
I must admit I have not done a lot of trigger creation, most of them simple checks. The examples I have found ion BOL have not helped me much.
How can I do this. CAN an 'update' be done on on the inserted record?
Greetz,
Hans Brouwer -
create table Table1(
K1 varchar(10),
K2 BIT default 0)
go
create trigger tr_Table1 on Table1 after insert
as
begin
Update T Set K2 = 1
From Table1 T join inserted i on i.K1 = T.K1
where Left(i.K1,1) = 'x'
end
go
insert into Table1(K1) values ('NoX')
insert into Table1(K1) values ('XOut')
insert into Table1(K1) values ('XTension')
insert into Table1(K1) values ('Nothing')
insert into Table1(K1) values ('ShouldBe0')
select * from Table1
K1 K2
---------- ----
NoX 0
XOut 1
XTension 1
Nothing 0
ShouldBe0 0
HTH
* Noel -
Good day,
I would suggest using a computed column IOF trigger ... 🙂
Like so (copying noelds sample ):
The CAST is just to get the bit datatype for the computed column.
CREATE TABLE Table1(
K1 VARCHAR(10),
K2 AS (CAST((CASE WHEN LEFT(K1,1) = 'X' THEN 1 ELSE 0 END) AS BIT)) )
INSERT INTO Table1(K1) VALUES ('NoX')
INSERT INTO Table1(K1) VALUES ('XOut')
INSERT INTO Table1(K1) VALUES ('XTension')
INSERT INTO Table1(K1) VALUES ('Nothing')
INSERT INTO Table1(K1) VALUES ('ShouldBe0')
SELECT * FROM Table1
DROP TABLE Table1
/rockmoose
You must unlearn what You have learnt -
I would suggest using a computed column IOF trigger
It is all matter of the application. Everything has pros and cons. Some times for production systems you might have to be flexible to change that requeriment. You can change Triggers code on the fly but will be forced to LOCK people out to change the definition of the Table
On the other hand you could use Instead of triggers as well should the specs are very complicated.
Speed is in favor of the computed column for inserts but if you are reading a lot of records the materialization favors the use of the trigger
I could be going on and on but I hope I prove my point about "Application dependency of implementation"
* Noel -
True Noel, this is a requirement question for the application.
And I was wrong in generalizing that broadly...
Triggers is IMO a "last resort" for enforcing business rules, but ok, sometimes - "Application dependency of implementation"
Another possibility could be to use a view...
create table Table1(
K1 varchar(10))
go
create view vTable1 as
select
K1,
CASE WHEN LEFT(K1,1) = 'x' THEN 1 ELSE 0 END AS K2
go
/rockmoose
You must unlearn what You have learnt -
Tnx both of you. I started with asking for a trigger, 'cause that was the first thing I thought of. Never thought of using the formula property for this. I only know this property being used from AS.
I use the calculated field to accomplish my goal, since this fits best in the proces where it's to be used.
Tnx again
Greetz,
Hans Brouwer
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply