March 2, 2009 at 3:27 pm
I have 2 tables:
--CREATE MyTable
create table MyTable
( Student varchar(10), course varchar(10), credit varchar(2)
, gradeno int, remark varchar(10)
)
go
--INSERT SAMPLE DATA
insert MyTable select 'Steve', 'ASM01', 3, 50,0
insert MyTable select 'Steve', 'ASM01', 3, 43,0
insert MyTable select 'Bob', 'ASM01', 3, 0,0
insert MyTable select 'Bob', 'OB01', 3, 23,0
insert MyTable select 'Bob', 'OB01', 3, 59,0
insert MyTable select 'Bob', 'ASM01', 3, 100,0
insert MyTable select 'Andy', 'OB01', 3, 0,0
insert MyTable select 'Andy', 'ASM01', 3, 50,0
insert MyTable select 'Andy', 'ASM01', 3, 10,0
insert MyTable select 'Andy', 'OB01', 3, 70,0
insert MyTable select 'Andy', 'OB01', 3, 0, 'ABS'
go
--CREATE DestinationTable
CREATE TABLE DestinationTable
( Student varchar(10), course varchar(10), credit varchar(2), Grades int,
Grade varchar(10), Points int, Quality int
)
GO
I now declared a trigger, which will update DestinationTable, whenever data is being inserted in MyTable. The Trigger is:
--Create Trigger
CREATE TRIGGER UpdateDetails
ON MyTable
AFTER UPDATE
AS
BEGIN
INSERT INTO DestinationTable (Student, course, credit, Grades, Grade, Points, Quality)
select Student, course, credit, sum(gradeno) as Grades
, case
when sum(gradeno) BETWEEN 91 and 100 then 'A+'
when sum(gradeno) BETWEEN 83 and 90 then 'A'
when sum(gradeno) BETWEEN 75 and 82 then 'B+'
when sum(gradeno) BETWEEN 67 and 74 then 'B'
when sum(gradeno) BETWEEN 59 and 66 then 'C+'
when sum(gradeno) BETWEEN 51 and 58 then 'C'
when sum(gradeno) BETWEEN 43 and 50 then 'D+'
when sum(gradeno) BETWEEN 35 and 42 then 'D'
when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
when sum(gradeno) BETWEEN 0 and 34 then 'F'
else 'NULL'
end 'Grade'
, case
when sum(gradeno) BETWEEN 91 and 100 then '8'
when sum(gradeno) BETWEEN 83 and 90 then '7'
when sum(gradeno) BETWEEN 75 and 82 then '6'
when sum(gradeno) BETWEEN 67 and 74 then '5'
when sum(gradeno) BETWEEN 59 and 66 then '4'
when sum(gradeno) BETWEEN 51 and 58 then '3'
when sum(gradeno) BETWEEN 43 and 50 then '2'
when sum(gradeno) BETWEEN 35 and 42 then '1'
when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Points'
, case
when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Quality'
from Inserted
group by Student, course, credit
ENDPlease point out me the error in Trigger!
March 4, 2009 at 3:22 am
mail2payan (3/2/2009)
I have 2 tables:I now declared a trigger, which will update DestinationTable, whenever data is being inserted in MyTable. The Trigger is:
--Create TriggerCREATE TRIGGER UpdateDetails
ON MyTable
AFTER UPDATE
AS
BEGIN...END
Please point out me the error in Trigger!
You say you want the trigger to operate 'whenever data is being inserted in MyTable' however your trigger code states the AFTER UPDATE directive.
Shouldn't it be AFTER INSERT instead?
Just an idea.
Cheers
Pierrick
Kind regards
Pierrick
March 4, 2009 at 6:27 am
mail2payan (3/2/2009)
I now declared a trigger, which will update DestinationTable, whenever data is being inserted in MyTable.
Actually, your trigger as written will insert into DestinationTable whenever data is been updated in MyTable
Can you explain in detail what you want to happen to DestinationTable and when?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 8:10 am
Whenever the data is being inserted in MyTable, the Trigger will automatically exceuted and filled the DestinationTable with following data:
--------------------------------------------------
Student course credit Grades Grade Points Quality
--------------------------------------------------
Andy ASM01 3 60 C+ 4 12
Andy OB01 3 70 B 5 15
Bob ASM01 3 100 A+ 8 24
Bob OB01 3 82 B+ 6 18
Steve ASM01 3 93 A+ 8 24
--------------------------------------------------
This data is being inserted on the basis of sample data i.e. being given in my first post.
I made a small interchange in the trigger:
CREATE TRIGGER UpdateDetails
ON MyTable
FOR INSERT, UPDATE
AS
BEGIN
INSERT INTO DestinationTable ( Student, course, credit, gradeno.....)
select Student, course, credit, sum(gradeno)
.......
from Inserted
group by Student, course, credit
END
But the output in destinationTable is coming as:
--------------------------------------------------
Student course credit Grades Grade Points Quality
--------------------------------------------------
Steve ASM01 3 50 A 2 6
Steve ASM01 3 43 A 2 6
Bob ASM01 3 0 F 0 0
Bob OB01 3 23 F 0 0
Bob OB01 3 59 C+ 4 12
Bob ASM01 3 100 A+ 8 24
Andy OB01 3 0 F 0 0
Andy ASM01 3 50 A 2 6
Andy ASM01 3 10 F 0 0
Andy OB01 3 70 B 5 15
Andy OB01 3 0 0 0 0
--------------------------------------------------
Hope, I can make you understand where is my problem??
March 4, 2009 at 8:17 am
mail2payan (3/4/2009)
Hope, I can make you understand where is my problem??
Not quite. What should happen when a row is inserted into MyTable? What should happen when that row is updated?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 8:35 am
Let me explain you in details:
Suppose, am entering followin data in MyTable:
insert MyTable select 'Steve', 'ASM01', 3, 50,0
insert MyTable select 'Steve', 'ASM01', 3, 43,0
Go
Now the Trigger which is attached with MyTable will now be automatically be excuted and the following data will automatically be inserted in DestinationTable:
--------------------------------------------------
Student course credit Grades Grade Points Quality
--------------------------------------------------
Andy ASM01 3 60 C+ 4 12
--------------------------------------------------
But, now data is automatically being inserting in DestinationTable, but in following way:
--------------------------------------------------
Student course credit Grades Grade Points Quality
--------------------------------------------------
Steve ASM01 3 50 A 2 6
Steve ASM01 3 43 A 2 6
--------------------------------------------------
Hope now you understand.
March 4, 2009 at 8:40 am
Ok, so on insert, a row must be inserted into Destination table if it doesn't exist and updated if it does? Which columns determine the matching row in DestinationTable?
What must happen if MyTable is updated?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 8:47 am
Which columns determine the matching row in DestinationTable?
Sorry, I did not get this question.
For update I used FOR INSERT, UPDATE syntax to make the trigger fire for both events means, whenever there is some update in MyTable, changes will occur in DestinationTable based on the update in MyTable.
March 4, 2009 at 9:07 am
mail2payan (3/4/2009)
Which columns determine the matching row in DestinationTable?
Sorry, I did not get this question.
When rows are inserted into MyTable, the matching rows in DestinationTable are updated with cumulative totoals. Is that correct?
What columns do I use from MyTable to find matching rows in DestinationTable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 9:14 am
GilaMonster (3/4/2009)
mail2payan (3/4/2009)
Which columns determine the matching row in DestinationTable?
Sorry, I did not get this question.
When rows are inserted into MyTable, the matching rows in DestinationTable are updated with cumulative totoals. Is that correct?
What columns do I use from MyTable to find matching rows in DestinationTable?
Yes Ma'm, when rows are inserted into MyTable, the matching rows in DestinationTable are updated with cumulative totals.
student, course and credit are the matching rows and rest rows in DestinationTable will be updated based on the calculation i.e. the SUM and CASE part.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply