April 7, 2011 at 1:36 am
Hi
I have 3 tables t_order, t_order_his, t_order_key.
(1) t_order table
CREATE TABLE [dbo].[t_order](
[id_order] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[date_document] [datetime] NULL,
[description] [varchar](20) NULL,
[order_ref] [varchar](15) NULL,
[active] [bit] NOT NULL,
[date_inserted] [datetime] NOT NULL,
[id_user_inserted] [int] NULL,
[date_modified] [datetime] NULL,
[id_user_modified] [int] NULL,
[date_deleted] [datetime] NULL,
[id_user_deleted] [int] NULL,
(2) t_order_his table :table to keep history for table t_order
CREATE TABLE [dbo].[t_order_his](
[id_order_his] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[id_order] [int] NULL,
[date_document] [datetime] NULL,
[description] [varchar](20) NULL,
[order_ref] [varchar](15) NULL,
[active] [bit] NOT NULL,
[date_inserted] [datetime] NOT NULL,
[id_user_inserted] [int] NULL,
[date_modified] [datetime] NULL,
[id_user_modified] [int] NULL,
[date_deleted] [datetime] NULL,
[id_user_deleted] [int] NULL,
[trigger_mode] [char](1) NOT NULL,
(3) t_order_key : table to create order_ref
CREATE TABLE [dbo].[t_order_key](
[id_order_key] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[year] [int] NULL,
[serial_number] [int] NULL,
Now I have 3 triggers on t_order:
--- (1) insert trigger ----
ALTER TRIGGER [dbo].[tr_logging_i_order] ON [dbo].[t_order]
FOR INSERT AS
BEGIN
SET NOCOUNT ON;
--- (A) order_ref
declare @key int
begin transaction
select @key = serial_number
from t_order_key
where year = YEAR(getDate())
update t_order_key
set serial_number = @key
where year = YEAR(getDate())
commit transaction
update t_order
set order_ref = 'MF-' + RIGHT('000'+ CONVERT(VARCHAR,YEAR (getDate))),4) + '-' + RIGHT('000000'+ CONVERT(VARCHAR,@key),7)
from
inserted i
where
i.id_order = t_order.id_order
--- (B) fill table t_order_his
insert into t_order_his
( id_order,
, date_document
, description
, order_ref
, active
, date_inserted
, id_user_inserted
, date_modified
, id_user_modified
, date_deleted
, id_user_deleted
, trigger_mode
)
select
id_order,
, date_document
, description
, order_ref
, active
, date_inserted
, id_user_inserted
, date_modified
, id_user_modified
, date_deleted
, id_user_deleted
, 'I'
from inserted
END
--- (2) update trigger ----
ALTER TRIGGER [dbo].[tr_logging_u_order] ON [dbo].[t_order]
FOR UPDATE AS
BEGIN
SET NOCOUNT ON;
--- (A) fill table t_order_his
insert into t_order_his
( id_order,
, date_document
, description
, order_ref
, active
, date_inserted
, id_user_inserted
, date_modified
, id_user_modified
, date_deleted
, id_user_deleted
, trigger_mode
)
select
id_order,
, date_document
, description
, order_ref
, active
, date_inserted
, id_user_inserted
, date_modified
, id_user_modified
, date_deleted
, id_user_deleted
, 'U'
from inserted
END
-----------------
PROBLEM
-----------------
(1) In insert trigger i have to get a serial number to modify the field order-ref. The last used serial is in the t_malfunction_key table.
Is this the right way to do it... It works but???
(2) I want my t_order_his to be logical in order. Now because i change the field order_ref i have a problem. Flow insert a record
(A) The trigger tr_logging_i_order is fired
(B) field order_ref is updated
(C) because of update order_ref the trigger tr_logging_u_order is fired
So in table t_order_his is a record created with trigger_mode =
update.
(D) now part 2 of insert trigger is executed. So now in table t_order_his
is a record inserted with trigger_mode = I
So what do i have to change that there is only 1 record in t_order_his with trigger_mode = Inserted?
Thank you
Wheel
April 7, 2011 at 6:00 am
This was removed by the editor as SPAM
April 7, 2011 at 7:44 am
Hi
This stays a problem.
- insert in t_order
I want that the for insert trigger tr_logging_i_order creates 1 record with trigger_mode 'I' (insert) in table t_order_his.
But because in the for insert trigger tr_logging_i_order we have to update the order_ref. The for update trigger is fired and this inserts also a record in the table t_order_his. So after the insert this is in the t_order_his
=> 2 records
id_order_his id_order order_ref trigger_mode
27 88 MF-2011-0000088 U
28 88 NULL I
I want after an insert only 1 record
id_order_his id_order order_ref trigger_mode
27 88 MF-2011-0000088 I
How can i do this?
Thank you
Wheel
April 7, 2011 at 8:00 am
This was removed by the editor as SPAM
April 7, 2011 at 8:43 am
No almost,
in t_order_his is now one record
but in t_order there is no record because of instead of trigger.
This insert in t_order has to happen ??
Other suggestion??
Thank you
Wheel
April 8, 2011 at 4:54 am
This was removed by the editor as SPAM
April 8, 2011 at 5:33 am
yes it works
sorry the insert in t_order i didn't seen
perfect
Thank you
Wheel 😉
April 12, 2011 at 2:28 am
Hi,
Another question follows :w00t:
now i want to use a stored procedure to insert a record in table t_order with this instead trigger
INSERT t_order (
date_document
, description
, active
, date_inserted
, id_user_inserted
)
values ('2011-03-15','testing',1,'2011-03-15',123)
id_order is the unique identity field. Now i want to know which key he has inserted.
I tought SCOPE_IDENTITY would be the answer. But it gives null.
I need to know the key of t_order (id_order) in the current session across all scopes.
What do i have to use to succeed?
Thank you
Wheel
April 12, 2011 at 6:23 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply