Needed Help for Update/Insert Trigger
-
Hi there, I have been assigned to write my first trigger and having browsed around with the previous posts. I still have no idea what I should do in order to create my trigger. Here is the requirements: It has to "support integrity in multi-user environment with concurrent user access to data". Would be appreciate if anyone out there can give my an example and point out which areas I should pay attention for creating the triggers.
Many thanks,
-
Look for create trigger under Books online. It has many examples also.
-
Arthur,
The requirement "It has to "support integrity in multi-user environment with concurrent user access to data". " is more like a Business Requirement. Next step according to "Analyzing Requirements and Defining a Solution architecture" is to collect user requirements, usage cases, then develop technical specifications and a conceptual model. Conceptual model will take individual facts and put it on a diagram with relations. After that it is very easy to create a logical and then a physical design.
In your particular case as one example:
User Requirments: " I need to update this table while Jane is updating too" or "I need to read from this table while Jane is updating and see only rows that were not incerted"
Usage Cases: "I work from this workstation and connect using this app, Jane workes from her workstation and uses TheSecondApp that supports transactions"
Technical Specifications "Trigger should insert this or prevent that"
Conceptual Model: Look UP on the Web by the keywords "Object Role Modeling"
Logical Design: Code of the trigger with individual actions listed rather then T-SQL with a schema of the tables presented as objects, high-level presentation
Physical Design: Some T-SQL code, mostly statements for the trigger with a database diagram that lists involved tables with their fields.
This is more or less what I would do. And to describe it shortly: Talk to users, see what they do, create a list of tasks for a trigger then code it.
Yelena
Regards,Yelena Varsha
-
Hi there, I finally composed my 1st trigger and it works fine. However, it doesn't work if I defined a table with compound primary keys. The following is my test code, please help.
-- this works fine
CREATE TABLE tblNullable (ColId INT PRIMARY KEY, NullableValue1 INT, NullableValue2 INT)
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Nullable_UI' AND type = 'TR')
DROP TRIGGER Nullable_UI
GO
CREATE TRIGGER Nullable_UI ON tblNullable
FOR INSERT, UPDATE
AS
BEGIN
-- if ColId existed in tblNullable table, raise error and rollback
IF EXISTS (SELECT NULL FROM tblNullable n JOIN inserted i ON n.NullableValue1 = i.NullableValue1 AND n.NullableValue2 = i.NullableValue2 WHERE n.ColId <> i.ColID)
BEGIN
RAISERROR 50001 'Cannot insert duplicate key'
ROLLBACK TRANSACTION
RETURN
END
END
-- does not work for compound primary key table definition
CREATE TABLE tblNullable (ColId1 INT, ColId2 INT, NullableValue1 INT, NullableValue2 INT
CONSTRAINT pk_Nullable PRIMARY KEY (ColId1, ColId2))
GO
Even though I have modified the select statement to corp with the compound primary keys, it could only satisfied some of the test cases. Anyone could help?
-
Arthur,
Can you not just define a unique key on column nullablevalue1 & nullablevalue2
-
I cannot change the table definition because it is defined by a client's database team. Actually the real table definition has more than 5 columns which are defined as nullable column with unique constraint for each of them. The client agreed that we can remove those unique constraint but reluctant to change the table definition. Is this make sense? Thanks,
-
There is something a bit odd in this Trigger. MAybe I do not quiet understand what you are trying to do.
If it is triggered by an update statement the Row will already exist( or it should ) so the trigger will always rollback the update.
Also should your "If exists" statement not just be an inner join between inserted and target table if you are looking for rows that already exist.
Basically, if this trigger disallows inserting already present primary key, this is not necessay primary key constraint already takes care of that.
I may be barking up the wrong tree here. (Late afternoon short work week )
-
One other thing to Remember, a Null <> Null so you could have the Following Row a, b, c, Null, d in the table And sitll insert a, b, c, null, d because although the first three and last Columns are equal the fourth is not because Null never equals Null
-
Mmmm... The following are the test cases, they are the expected results for non-compound primary key table definition:
-- test cases for insert with results
INSERT INTO tblNullable VALUES (1, 1, 1)
GO
(1 row(s) affected)
INSERT INTO tblNullable VALUES (2, 1, NULL)
GO
(1 row(s) affected)
INSERT INTO tblNullable VALUES (2, 1, 1)
GO
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__tblNullable__334B710A'. Cannot insert duplicate key in object 'tblNullable'.
The statement has been terminated.
INSERT INTO tblNullable VALUES (3, 1, 1)
GO
Server: Msg 50001, Level 16, State 1, Procedure Nullable_UI, Line 10
Cannot insert duplicate entry
INSERT INTO tblNullable VALUES (3, NULL, 1)
GO
(1 row(s) affected)
INSERT INTO tblNullable VALUES (4, 2, 1)
GO
(1 row(s) affected)
-- test cases for update with results
UPDATE tblNullable SET NullableValue2 = 1 WHERE ColId = 2
GO
Server: Msg 50001, Level 16, State 1, Procedure Nullable_UI, Line 10
Cannot insert duplicate entry
UPDATE tblNullable SET NullableValue1 = NULL, NullableValue2 = 2 WHERE ColId = 2
GO
(1 row(s) affected)
UPDATE tblNullable SET NullableValue1 = NULL WHERE ColId = 6
GO
(0 row(s) affected)
However, it breaks when I tried to apply on a table with compound primary key. You are right to say that the primary key constraint shall taking care of the check when attempt to insert a new row, however, I believe there will be a problem for update statement.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply