March 11, 2009 at 3:54 pm
I have table, where if data is inserted in two attempts, after that, noone can neither insert or update the data in the following table,e.g.
First am just inserting the following value in MyTable:
------------------------------------
ID Student Course Marks
------------------------------------
1 Andy ASM
2 Bob ASM
3 Andy OB
4 Bob OB
------------------------------------
Here, you can see, Marks column left blank, and other column being filled up, on next attempt, marks will be inserted only once and if someone wants to update or delete any of the columns on third attempt, the TRIGGER should restrict it.
How can this be done through TRIGGER??
March 11, 2009 at 4:11 pm
this is usually done at the presentation layer, not at the database level.
this kind of logic is hard to do...what is meant by 3rd time? ever? or 3 times in a web session? 20 minutes?
is it by user , so one user can try, but a different user has his own 3 tries?
assuming it's 3 strikes you are out,to do that you'd have to have a counter column for each row, or a separate table that counts attempts or something and an INSTEAD OF trigger to update the attempts .
all in all, a bad idea, and you should consider doing this in an applicaiton instead.
Lowell
March 11, 2009 at 4:19 pm
Am talking about 3 strikes.
How can I use this counter column or the INSTEAD OF?
I don't want to use in my ASP as if I have to make changes in future, I don't have to change the codes of ASP, just alter the database, that's my business logic.
March 11, 2009 at 6:19 pm
Am talking about 3 strikes.
How can I use this counter column or the INSTEAD OF?
I don't want to use in my ASP as if I have to make changes in future, I don't have to change the codes of ASP, just alter the database, that's my business logic.
Can't I do this:
CREATE TRIGGERdbo.Check_Marks
ONMyTable
AFTERUPDATE
AS
IF EXISTS(SELECT * FROM MyTable WHERE Student, Course, Marks IS NOT NULL)
BEGIN
RAISERROR('Please contact Admin for Updates', 16, 1)
ROLLBACK TRAN
END
Here, it'll check if marks were entered before change if they were roll back the change!
March 11, 2009 at 7:26 pm
i'm not sure what you want for the actual rule...you say both insert and update, and i don't know if you are talking about multiple rows or editing a single row.
here's my single row assumption:
you can only edit a record 3 times total, with the insert counting as one row, OR if all 3 fields are not null, rais ean error, like you did in your example.
[font="Courier New"]
CREATE TABLE MyTable(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Student VARCHAR(30),
Course VARCHAR(4),
MARKS DECIMAL(10,4),
EditAttempts INT DEFAULT 1 )
GO
CREATE TRIGGER dbo.Check_Marks ON MyTable
FOR UPDATE
AS
BEGIN
--check to see if tis would be a third attempt at editing for ANY of the INSERTED rows.
--what is the violation? Marks cannot be null?
IF EXISTS(SELECT * FROM INSERTED
WHERE (Student IS NOT NULL
AND Course IS NOT NULL
AND Marks IS NOT NULL)
OR INSERTED.EditAttempts > 2)
BEGIN
RAISERROR('Please contact Admin for Updates', 16, 1)
ROLLBACK TRAN
END
--increment EditAttempts no matter what.
UPDATE MyTable
SET MyTable.EditAttempts = MyTable.EditAttempts + 1
FROM INSERTED
WHERE MyTable.ID = INSERTED.ID
END
GO
INSERT INTO MyTable(Student,Course)
SELECT 'Andy ','ASM' UNION ALL
SELECT 'Bob','ASM' UNION ALL
SELECT 'Andy','OB' UNION ALL
SELECT 'Bob','OB'
UPDATE MyTable
SET Course = 'ASM1'
WHERE Course = 'ASM'
UPDATE MyTable
SET Course = 'ASM2'
WHERE Course = 'ASM1'
UPDATE MyTable
SET Course = 'ASM3',Marks = 87
WHERE Course = 'ASM2'
SELECT * FROM MyTable
DROP TABLE mytable[/font]
Lowell
March 12, 2009 at 5:28 am
Sir, If am doing:
UPDATE MyTable
SET MARKS = 87
WHERE Student = 'Andy' and Course = 'ASM'
Its not allowing to enter the data on 2nd strike and showing the ERROR message!!
I cant insert any data in MARKS column, everything working fine with other columns...but not with MARKS column
March 12, 2009 at 5:52 am
i had to base the business rule on the prototype trigger you showed me, plus the 3 strikes your out rule.
your trigger implied if 3 specific fields were NOT NULL, then raise error. if that's not right, simply change the IF EXISTS statement to what you are after...only 3 edits? then
IF EXISTS(SELECT * FROM INSERTED WHERE INSERTED.EditAttempts > 2)
Lowell
March 12, 2009 at 7:29 am
Thanks A Lot!
Now it's working superb...THANKs A LOT!
Can I ask you another question?
Whenever am trying to populate this MyTable through updating from multiples tables,am getting the following error:
Key column information is insufficient or incorrect. Too many rows were affected by update.
Say for example:
I have Course_Details table:
----------------------------------------------------------------------------------------
Course | Name | Term
----------------------------------------------------------------------------------------
ASM | Advance System Management | T1
OB | Organistaional Behavior | T2
----------------------------------------------------------------------------------------
Student_Details table:
--------------------------------------------
Student | DOB | Term
--------------------------------------------
Andy | 26/03/1985 | T1
Bob | 13/07/1986 | T1
--------------------------------------------
Whenever am going to populate the Course_Details, through a TRIGGER:
CREATE TRIGGER Insert_Student
ON Course_Details
After INSERT
AS
BEGIN
INSERT INTO MyTable (Student, Course)
SELECT s.student, c.course
FROM INSERTED c INNER JOIN
dbo.Student_Details s ON c.Term = s.Term
END
But Date Inserting n Course_Details column TWICE and also same effect shoein in MyTable!
I should not ask another question, but can you give me some solution here?
Note: There is no other INSERT TRIGGER in this Course_Details table.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply