June 1, 2008 at 1:09 pm
Hi ,
I am new to trigger.
I want to create a insert trigger for a table(table 1) ,in which when data is inserted, my another table(table 2) should get updated by fetching the data from table 1.
Table 1 Name : shifts
fields are :
var time_s
Here shifts means company i.e. shift1,shift2 or shift 3
between each shift there are 2 tea breaks, 1 lunch break & 1 recess
var contains entries like Shift1_time[1]
to Shift1_time[30]
Shift2_time[1]
to Shift2_time[30]
Shift3_time[1]
to Shift3_time[30]
these are total 90 entries,
time is in seconds, time format is given below
explaination is as follows :
* means Shift 1 or 2 or 3
Event HH MM SS
Shift Start Shift*_time[1] Shift*_time[2] Shift*_time[3]
Shift End Shift*_time[4] Shift*_time[5] Shift*_time[6]
Tea-1 start Shift*_time[7] Shift*_time[8] Shift*_time[9]
Tea-1 End Shift*_time[10] Shift*_time[11] Shift*_time[12]
Lunch Start Shift*_time[13] Shift*_time[14] Shift*_time[15]
Lunch End Shift*_time[16] Shift*_time[17] Shift*_time[18]
Tea-2 Start Shift*_time[19] Shift*_time[20] Shift*_time[21]
Tea-2 End Shift*_time[22] Shift*_time[23] Shift*_time[24]
Recess Start Shift*_time[25] Shift*_time[26] Shift*_time[27]
Recess end Shift*_time[28] Shift*_time[29] Shift*_time[30]
What I want to do is ,whenever client updates above shift table , an insert trigger should get fired which will fetch data from this shift table , process it in format HH:MM:SS ( bcaz above table contains time in three seperate formats.
for example : if shift starts at 6:15:00 and ends 3:30:00
this format i want, by client to me in below format
Shift Start Shift1_time[1] = 6 ( This is HH)
Shift1_time[2] = 15 (this is MM)
Shift1_time[3] = 00 (this is ss)
Shift End Shift1_time[4] = 3
Shift1_time[5] = 30
Shift1_time[6] = 00
and so on...
So insert trigger should fetch data from above table shift , process it and put data in HH:MM:SS format in Table "tblShifts" which is i am going to use in my project.
Table 2 : tblShifts structure
fields :
shiftId,shiftName, startTime, endTime, tea1_Start, tea1_End,tea2_Start,lunch_Start,lunch_End,recess_Start,recess_End
shiftId shiftName startTime .................
1 shift1 ....................
2 shift2 .....................
3 shift3 ...........................
Can anybody give suggestion , for creating trigger..................:)
June 1, 2008 at 8:23 pm
Any chance of changing the design to something a bit more normal instead of storing seconds for hours, minutes, and seconds?
Also, what have you tried? Generally speaking, you should try writing a SELECT statement before you even think of converting it into a trigger?
--Jeff Moden
June 2, 2008 at 6:49 am
Hi,
got ur reply.
But this is the format provided by my client.
I have tried to write trigger, but didn't got success.
First i have tried to identify Shift i.e. shift 1 or shift 2 or shift 3
this is done by creating a cursor and doing substring stuff on field [VAR]
i have written a slect query in which i am counting rows with Shift1
this gives me 30 rows of shift1 and so on i can get next 30 rows for next shift.
But first i am ttying for first 30 rows of shift1.
From here main problem starts,
my logic is : in 30 rows entries are shift start and end time means 6 entries(3 for start,3 for end)
tea1start,tea1end 6 entries(3,3)
tea2start,tea2end, 6 entries(3,3)
lunchstart,lunchend, 6 entries(3,3)
recess_Start,recess_End 6 entries(3,3)
Total 30 entries
I have to go row by row , which chould be achieved by use of cursor
I don't understand how to move ahead from here.
If i use cursors, then this will increase no. of cursors........
Any suggestion ?
plz reply.........
June 2, 2008 at 2:23 pm
Based on the info you have provided, you don't need an INSERT trigger. Your client has a poorly designed table but it is a static table -- the data, once inserted, is not expected to change very often. And, I assume, the data has already been inserted into that table, so an INSERT trigger would be useless anyway. Just manually translate that data into the form of the second table and insert it. You might then want an UPDATE trigger on the first table to keep your table up to date in case the shift times ever do change.
The UPDATE trigger shouldn't be too difficult.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
June 2, 2008 at 10:51 pm
Hi Rahul,
Can you post the Table Structure as a create Table query for the two tables along with some sample Datas as insert query and expected Results in the Table2 for the inserted Rows in Table1.
Rajesh
June 29, 2008 at 1:25 am
Hello friends,
Finally I have created the required trigger, but I have a problem.
I have written 2 cursors, 30 if else statements. Plz check first 3 if statements.
IF @calc1 = '1'
--SET @Shift_Start = @val
print @val
ELSE IF @calc1 = '2'
--SET @Shift_Start = @Shift_Start + ':' + @val
print @val
ELSE IF @calc1 = '3'
--begin
--SET @Shift_Start = @Shift_Start + ':' + @val
print @val
--end
********************
what I want is
In first IF i am getting "@Shift_Start" (HH)
In Second ELSE IF i want to concatenate "@Shift_Start" which i got in first IF. "@Shift_Start= @Shift_Start+ : + @val" (HH:mm)
In Third ELSE IF i want to concatenate "@Shift_Start" which i got in second ELSE IF. "@Shift_Start= @Shift_Start+ : + @val" (HH:mm:ss)
So that i will get a complete string HH:mm:ss , a time value.
But problem is I am not able to get previous value on each FETCH operation. means, in third ELSE IF when i conatenate and print string it prints (suppose 3 values ,7,15,30, I want 7:15:30, but it prints " : :30"
It is not giving me privious values.
I am not able get the whole string. Is there any concept of global or local variable in SQL.
How I will get concatenated value ?
Any suggestion...
Whole trigger is given below.
////////////////////////////////////////////////////////////////////////////////
USE [128Stages]
GO
/****** Object: Trigger [dbo].[shift_Insert_Trig] Script Date: 06/25/2008 11:22:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER TRIGGER [dbo].[shift_Insert_Trig]
ON [dbo].[shift_DATA]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @tempSubString1 VARCHAR(128)
DECLARE @tempSubString2 int
Declare @calc1 varchar(128)
Declare @val varchar(128)
DECLARE @Shift_Start VARCHAR(128)
DECLARE @Shift_End VARCHAR(128)
DECLARE @Tea1_Start VARCHAR(128)
DECLARE @Tea1_End VARCHAR(128)
DECLARE @Lunch_Start VARCHAR(128)
DECLARE @Lunch_End VARCHAR(128)
DECLARE @Tea2_Start VARCHAR(128)
DECLARE @Tea2_End VARCHAR(128)
DECLARE @Recess_Start VARCHAR(128)
DECLARE @Recess_End VARCHAR(128)
--**********************************************
-- CURSOR --
DECLARE get_Shift_Id CURSOR
FOR SELECT DISTINCT SUBSTRING([VAR], 12, 6)
FROM inserted
--declare Cursor curShift for select distinct shiftIds from inserted
--=========================================
OPEN get_Shift_Id
FETCH NEXT FROM get_Shift_Id INTO @tempSubString1
WHILE @@FETCH_STATUS <> -1
--print @tempSubString1
BEGIN
DECLARE get_Time_Val CURSOR
FOR SELECT SUBSTRING([VAR], 24, PATINDEX('%]%', [VAR]) - 24) AS myShift, SUBSTRING([VALUE], 1, PATINDEX('%.%', [VALUE]) - 1) as myTime
FROM inserted WHERE [VAR] LIKE '%' + @tempSubString1 + '%' --ORDER BY 'calc1' ASC
OPEN get_Time_Val
FETCH NEXT FROM get_Time_Val INTO @calc1 , @val
WHILE @@Fetch_Status <> -1
BEGIN
IF @calc1 = '1'
--SET @Shift_Start = @val
print @val
ELSE IF @calc1 = '2'
--SET @Shift_Start = @Shift_Start + ':' + @val
print @val
ELSE IF @calc1 = '3'
--begin
--SET @Shift_Start = @Shift_Start + ':' + @val
print @val --end
ELSE IF @calc1 = '4'
--SET @Shift_End = @val
print @val
ELSE IF @calc1 = '5'
--SET @Shift_End = @Shift_End + ':' + @val
print @val
ELSE IF @calc1 = '6'
--SET @Shift_End = @Shift_End + ':' + @val
print @val
ELSE IF @calc1 = '7'
--SET @Tea1_Start = @val
print @val
ELSE IF @calc1 = '8'
--SET @Tea1_Start = @Tea1_Start + ':' + @val
print @val
ELSE IF @calc1 = '9'
--SET @Tea1_Start = @Tea1_Start + ':' + @val
print @val
ELSE IF @calc1 = '10'
--SET @Tea1_End = @val
print @val
ELSE IF @calc1 = '11'
--SET @Tea1_End = @Tea1_End + ':' + @val
print @val
ELSE IF @calc1 = '12'
--SET @Tea1_End = @Tea1_End + ':' + @val
print @val
ELSE IF @calc1 = '13'
--SET @Lunch_Start = @val
print @val
ELSE IF @calc1 = '14'
--SET @Lunch_Start = @Lunch_Start + ':' + @val
print @val
ELSE IF @calc1 = '15'
--SET @Lunch_Start = @Lunch_Start + ':' + @val
print @val
ELSE IF @calc1 = '16'
--SET @Lunch_End = @val
print @val
ELSE IF @calc1 = '17'
--SET @Lunch_End = @Lunch_End + ':' + @val
print @val
ELSE IF @calc1 = '18'
--SET @Lunch_End = @Lunch_End + ':' + @val
print @val
ELSE IF @calc1 = '19'
--SET @Tea2_Start = @val
print @val
ELSE IF @calc1 = '20'
--SET @Tea2_Start = @Tea2_Start + ':' + @val
print @val
ELSE IF @calc1 = '21'
--SET @Tea2_Start = @Tea2_Start + ':' + @val
print @val
ELSE IF @calc1 = '22'
--SET @Tea2_End = @val
print @val
ELSE IF @calc1 = '23'
--SET @Tea2_End = @Tea2_End + ':' + @val
print @val
ELSE IF @calc1 = '24'
--SET @Tea2_End = @Tea2_End + ':' + @val
print @val
ELSE IF @calc1 = '25'
--SET @Recess_Start = @val
print @val
ELSE IF @calc1 = '26'
--SET @Recess_Start = @Recess_Start + ':' + @val
print @val
ELSE IF @calc1 = '27'
--SET @Recess_Start = @Recess_Start + ':' + @val
print @val
ELSE IF @calc1 = '28'
--SET @Recess_End = @val
print @val
ELSE IF @calc1 = '29'
--SET @Recess_End = @Recess_End + ':' + @val
print @val
ELSE IF @calc1 = '30'
--SET @Recess_End = @Recess_End + ':' + @val
print @val
-- Here we break while when calc1='30'
IF @calc1 = '30'
BREAK
ELSE
FETCH NEXT FROM get_Time_Val INTO @calc1 , @val
END -- while of get_Time_Val ends
CLOSE get_Time_Val
DEALLOCATE get_Time_Val
--*************************************
SET @tempSubString2= convert(int,substring(@tempSubString1,6,1))
-- IF @tempSubString1='Shift1'
-- BEGIN
-- UPDATE dbo.[tblShiftDetails]
-- SET tTimeFrom=@Shift_Start,tTimeTo=@Shift_End,tea1Start=@Tea1_Start,tea1End=@Tea1_End,tea2Start=@Tea2_Start,tea2End=@Tea2_End,lunchStart=@Lunch_Start,lunchEnd=@Lunch_End,recessStart=@Recess_Start,recessEnd=@Recess_End
-- WHERE (nShiftId=@tempSubString2)
-- END
print @tempSubString2
FETCH NEXT FROM get_Shift_Id
END -- while of get_Shift_Id
CLOSE get_Shift_Id
DEALLOCATE get_Shift_Id
END -- Trigger closed.
June 29, 2008 at 6:05 am
Please supply CREATE Table statements and sample data as Raja has requested, or read this article on how to get better results from these forums: http://qa.sqlservercentral.com/articles/Best+Practices/61537/.
[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]
June 29, 2008 at 7:28 am
ok, this is table structure script, on which i have written trigger.
The Field TIMESTAMP_S is in unix time format,
to convert it into sql format
DATEADD(s, TIMESTAMP_S, '19700101')
///////////////////////////////////
USE [POKAYOKE2]
GO
/****** Object: Table [dbo].[shift_DATA] Script Date: 06/29/2008 18:38:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[shift_DATA](
[PRJ] [varchar](128) NULL,
[ARV] [varchar](2) NULL,
[VAR] [varchar](128) NULL,
[CALCULATION] [int] NULL,
[TIMESTAMP_S] [int] NULL,
[TIMESTAMP_MS] [int] NULL,
[VALUE] [varchar](128) NULL,
[STATUS] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/////////////////////////////////////
Data to be Inserted
INSERT INTO dbo.shift_DATA values('128STAGE','Z8','PLC/Retain/Shift1_time[1]',0,1213609856,796,'7.0',1075970048)
INSERT INTO dbo.shift_DATA values ('128STAGE','Z8','PLC/Retain/Shift1_time[2]',0,1213609856,796,'15.0',1075970048)
INSERT INTO dbo.shift_DATA values ('128STAGE','Z8','PLC/Retain/Shift1_time[3]',0,1213609856,796,'0.0',1075970048)
INSERT INTO dbo.shift_DATA values ('128STAGE','Z8','PLC/Retain/Shift1_time[4]',0,1213609856,796,'2.0',1075970048)
INSERT INTO dbo.shift_DATA values ('128STAGE','Z8','PLC/Retain/Shift1_time[5]',0,1213609856,796,'15.0',1075970048)
INSERT INTO dbo.shift_DATA values ('128STAGE','Z8','PLC/Retain/Shift1_time[6]',0,1213609856,796,'0.0',1075970048)
June 30, 2008 at 7:42 pm
Plz , does anybody know the solution for this problem ?
Plz reply.
July 2, 2008 at 3:03 pm
Are you still looking for help on this?
I'll have to say that after reading through the post a couple of times, I'm still not quite sure what you are trying to do. It looks like you want to make an UPDATE to your table tblShiftDetails based on the values inserted into your table with the trigger. It would help a bunch if you could post the table DDL for tblShiftDetails and post examples of how the data will look before AND after the trigger runs based off of the sample INSERTS that you posted.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply