Insert Trigger
-
When record is inserted into my table I must put value from field C to field B. Field C is autonumber and value from field C is default value for field B which is part of primary key together with filed A.
A B C
-----------
x 10 10
y 11 11
a 12 12 Value from C always go to B.
I would like the solve a problem with insert trigger but I'm wery new in triggers
Some suggestions .....
Thanks
-
CREATE TRIGGER [trg_INSERT_Table1] ON [dbo].[Table1]
FOR INSERT AS
DECLARE @a VARCHAR(10)
DECLARE @b-2 INT
SET @a = (SELECT a FROM inserted)
SET @b-2 = (SELECT b FROM inserted)
UPDATE table1 SET c = @b-2 WHERE a = @a AND b = @b-2
--------------------------------------------------------------------------------------
THEN INSERT INTO TABLE1 (a) VALUES ('x')
--------------------------------------------------------------------------------------
Sorry did not read full post last time this is now a little better, hope this helps...
-
If C is an IDENTITY data type, it is always going to be unique. Why do you have B (which is the same as C) and A combined as the primary key?
-
Thanks James.
Short explanation:
Field A and B together is a primary key:
- A is database ID
- B is record ID
- C is used just for nuber generator for field B. (if B = 0)
This metod is used for copy data between databases. Field A and B is field in relations. When you are copying data from another database you must use a complex SP with @@identity if field B is autonumber, if not I use simple append query for both table (primary and child).
In any case, I must modify James solution. Value of Field B is changed with value of Field C only when field B is 0 (default value = 0), if field B is > 0 then data is copying, and value of field B must stay untached.
-
Now it makes more sense.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply