October 4, 2016 at 2:51 pm
Would you show me how to setup a trigger from the items I listed below for a t-sql 2012 database?
1.I want a trigger to fire on an insert to the customstudent table. The insert when need to check for:
a. The same personID value,
b. the same value for the column called value ='17', and
c. the attribute id = '9875'.
2.I would like to prevent the insert from occurring and/or possibly send an email message to a selected group of administrators saying an attempt was made to insert the second record.
3.The ddl is the following:
CREATE TABLE [dbo].[CustomStudent](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[enrollmentID] [int] NULL,
[attributeID] [int] NOT NULL,
[value] [varchar](50) NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
CONSTRAINT [PK_StudentData] PRIMARY KEY NONCLUSTERED
(
[customID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CustomStudent] ADD CONSTRAINT [DF_CustomStudent_GUID] DEFAULT (newid()) FOR [customGUID]
GO
ALTER TABLE [dbo].[CustomStudent] WITH NOCHECK ADD CONSTRAINT [FK_StudentData_Enrollment] FOREIGN KEY([enrollmentID])
REFERENCES [dbo].Enrollment
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomStudent] CHECK CONSTRAINT [FK_StudentData_Enrollment]
GO
ALTER TABLE [dbo].[CustomStudent] WITH NOCHECK ADD CONSTRAINT [FK_StudentData_Person] FOREIGN KEY([personID])
REFERENCES [dbo].Person
GO
ALTER TABLE [dbo].[CustomStudent] CHECK CONSTRAINT [FK_StudentData_Person]
GO
ALTER TABLE [dbo].[CustomStudent] WITH NOCHECK ADD CONSTRAINT [FK_StudentData_StudentAttribute] FOREIGN KEY([attributeID])
REFERENCES [dbo].CampusAttribute
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomStudent] CHECK CONSTRAINT [FK_StudentData_StudentAttribute]
GO
October 5, 2016 at 2:54 am
I'm going to admit here, triggers are not my forté. I rarely use them as our main database is third party, and repopulated every night (so we have no need for them on it), so likely there is a better solution, but wanted to give this a stag anyway.
Note that I've omitted the email part. The reason being that I'm not sure who will be running the insert, a user, an application, a DBA? Whoever it is, if you're firing an email within a trigger then you'll need ensure that whoever is doing the insert has permission to send emails in SQL server. if they don't, the insert will fail with an error saying that they don't have permission to do so. This is more of a start for you, and then you can finish it off for your needs.
Warning, I have DROP statements in my SQL, Don't run this on your production server/production database, it will drop your table!
USE DevtestDB;
GO
CREATE TABLE [dbo].[CustomStudent](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[enrollmentID] [int] NULL,
[attributeID] [int] NOT NULL,
[value] [varchar](50) NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
CONSTRAINT [PK_StudentData] PRIMARY KEY NONCLUSTERED
(
[customID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER StudentInsertCheck
ON CustomStudent
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomID INT,
@PersonID INT,
@Value VARCHAR(50),
@AttributeID INT;
SELECT @CustomID = I.customID,
@PersonID = I.personID,
@Value = I.[value],
@AttributeID = I.attributeID
FROM Inserted I
IF @Value = '17' BEGIN
RAISERROR(N'Value cannot have a value of 17.', 10, 1);
ROLLBACK;
END
IF @AttributeID = 9875 BEGIN
RAISERROR(N'Attribute ID cannot have a value of 9875.', 10, 1);
ROLLBACK;
END
IF (SELECT COUNT(customID) FROM CustomStudent sq WHERE sq.personID = @PersonID and sq.customID != @CustomID) != 0 BEGIN
RAISERROR(N'Person with ID already exists.', 10, 1);
ROLLBACK;
END
END
GO
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 1, 1, 12, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (2, 2, 1, 17, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (3, 3, 9875, 11, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 5, 9500, 16, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
SELECT *
FROM CustomStudent
/*
I'm just going to drop the table and recreate it instead, as I'm adding UNIQUE to the person ID.
Really, I'm just being lazy :)
*/
DROP TABLE CustomStudent
CREATE TABLE [dbo].[CustomStudent](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL UNIQUE,
[enrollmentID] [int] NULL,
[attributeID] [int] NOT NULL,
[value] [varchar](50) NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
CONSTRAINT [PK_StudentData] PRIMARY KEY NONCLUSTERED
(
[customID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE CustomStudent
ADD CONSTRAINT chk_value CHECK ([value] != '17');
ALTER TABLE CustomStudent
ADD CONSTRAINT chk_attributeid CHECK (attributeid != 9875);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 1, 1, 12, '05-Oct-2016', NEWID(), 1);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (2, 2, 1, 17, '05-Oct-2016', NEWID(), 1);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (3, 3, 9875, 11, '05-Oct-2016', NEWID(), 1);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 5, 9500, 16, '05-Oct-2016', NEWID(), 1);
SELECT *
FROM CustomStudent;
DROP TABLE CustomStudent;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2016 at 3:01 am
You haven't given any examples of exactly what you're trying to prevent, but from your description, it sounds as if you could do all this (except the e-mail) with CHECK and UNIQUE constraints.
John
October 5, 2016 at 3:17 am
John Mitchell-245523 (10/5/2016)
You haven't given any examples of exactly what you're trying to prevent, but from your description, it sounds as if you could do all this (except the e-mail) with CHECK and UNIQUE constraints.John
A good point. I've editted my post above, one for a trigger, and one with constraints.
The Trigger version has the INSERT statements wrapped in TRY CATCH's, as the batch will fail otherwise. Constraints don't cause this.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2016 at 7:19 am
Thom A (10/5/2016)
I'm going to admit here, triggers are not my forté. I rarely use them as our main database is third party, and repopulated every night (so we have no need for them on it), so likely there is a better solution, but wanted to give this a stag anyway.Note that I've omitted the email part. The reason being that I'm not sure who will be running the insert, a user, an application, a DBA? Whoever it is, if you're firing an email within a trigger then you'll need ensure that whoever is doing the insert has permission to send emails in SQL server. if they don't, the insert will fail with an error saying that they don't have permission to do so. This is more of a start for you, and then you can finish it off for your needs.
Warning, I have DROP statements in my SQL, Don't run this on your production server/production database, it will drop your table!
USE DevtestDB;GO
CREATE TABLE [dbo].[CustomStudent](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[enrollmentID] [int] NULL,
[attributeID] [int] NOT NULL,
[value] [varchar](50) NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
CONSTRAINT [PK_StudentData] PRIMARY KEY NONCLUSTERED
(
[customID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER StudentInsertCheck
ON CustomStudent
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomID INT,
@PersonID INT,
@Value VARCHAR(50),
@AttributeID INT;
SELECT @CustomID = I.customID,
@PersonID = I.personID,
@Value = I.[value],
@AttributeID = I.attributeID
FROM Inserted I
IF @Value = '17' BEGIN
RAISERROR(N'Value cannot have a value of 17.', 10, 1);
ROLLBACK;
END
IF @AttributeID = 9875 BEGIN
RAISERROR(N'Attribute ID cannot have a value of 9875.', 10, 1);
ROLLBACK;
END
IF (SELECT COUNT(customID) FROM CustomStudent sq WHERE sq.personID = @PersonID and sq.customID != @CustomID) != 0 BEGIN
RAISERROR(N'Person with ID already exists.', 10, 1);
ROLLBACK;
END
END
GO
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 1, 1, 12, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (2, 2, 1, 17, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (3, 3, 9875, 11, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 5, 9500, 16, '05-Oct-2016', NEWID(), 1);
END TRY
BEGIN CATCH
END CATCH
SELECT *
FROM CustomStudent
/*
I'm just going to drop the table and recreate it instead, as I'm adding UNIQUE to the person ID.
Really, I'm just being lazy :)
*/
DROP TABLE CustomStudent
CREATE TABLE [dbo].[CustomStudent](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL UNIQUE,
[enrollmentID] [int] NULL,
[attributeID] [int] NOT NULL,
[value] [varchar](50) NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
CONSTRAINT [PK_StudentData] PRIMARY KEY NONCLUSTERED
(
[customID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE CustomStudent
ADD CONSTRAINT chk_value CHECK ([value] != '17');
ALTER TABLE CustomStudent
ADD CONSTRAINT chk_attributeid CHECK (attributeid != 9875);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 1, 1, 12, '05-Oct-2016', NEWID(), 1);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (2, 2, 1, 17, '05-Oct-2016', NEWID(), 1);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (3, 3, 9875, 11, '05-Oct-2016', NEWID(), 1);
INSERT INTO CustomStudent (personID, enrollmentID, attributeID, [value], [date], customGUID, districtID)
VALUES (1, 5, 9500, 16, '05-Oct-2016', NEWID(), 1);
SELECT *
FROM CustomStudent;
DROP TABLE CustomStudent;
Be careful if you use this code as a starting point. It has scalar values being populated from the inserted table. This is very problematic because the inserted table can have more than 1 row. Triggers need to be set based. In this example using constraints is the right tool for the job and triggers are not. If you are going to stick with this in a trigger you should do your validation on the entire set instead of scalar variables like this example demonstrates.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2016 at 7:53 am
Sean Lange (10/5/2016)
Be careful if you use this code as a starting point. It has scalar values being populated from the inserted table. This is very problematic because the inserted table can have more than 1 row. Triggers need to be set based. In this example using constraints is the right tool for the job and triggers are not. If you are going to stick with this in a trigger you should do your validation on the entire set instead of scalar variables like this example demonstrates.
This is exactly why I said I'm not great at them 🙂 From the description, it sounds more like this is a single entry, but dataset based checks(and changing my trigger to do so want be too hard) would be better if multiple values will be passed.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2016 at 8:08 am
Thom A (10/5/2016)
Sean Lange (10/5/2016)
Be careful if you use this code as a starting point. It has scalar values being populated from the inserted table. This is very problematic because the inserted table can have more than 1 row. Triggers need to be set based. In this example using constraints is the right tool for the job and triggers are not. If you are going to stick with this in a trigger you should do your validation on the entire set instead of scalar variables like this example demonstrates.This is exactly why I said I'm not great at them 🙂 From the description, it sounds more like this is a single entry, but dataset based checks(and changing my trigger to do so want be too hard) would be better if multiple values will be passed.
That is something you should be proud of. 😉 Triggers are not really a tool that should be used all that often. I have worked on a fair number of systems that used them regularly and they are a constant source of pain because you forget about them. As a general rule I try to avoid them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2016 at 3:46 pm
Sean Lange (10/5/2016)
Thom A (10/5/2016)
Sean Lange (10/5/2016)
Be careful if you use this code as a starting point. It has scalar values being populated from the inserted table. This is very problematic because the inserted table can have more than 1 row. Triggers need to be set based. In this example using constraints is the right tool for the job and triggers are not. If you are going to stick with this in a trigger you should do your validation on the entire set instead of scalar variables like this example demonstrates.This is exactly why I said I'm not great at them 🙂 From the description, it sounds more like this is a single entry, but dataset based checks(and changing my trigger to do so want be too hard) would be better if multiple values will be passed.
That is something you should be proud of. 😉 Triggers are not really a tool that should be used all that often. I have worked on a fair number of systems that used them regularly and they are a constant source of pain because you forget about them. As a general rule I try to avoid them.
Triggers are a great tool and should probably be used more often for data integrity checks and/or data standardization. The sooner bad data is caught, the easier it is to accurately fix.
Of course triggers must be well written because of their having to execute within the same transaction as the DELETE | INSERT | UPDATE statement.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 5, 2016 at 3:56 pm
I interpreted the OP's requirement differently, i.e., a given personID cannot have more than one row with a value of '17' and an attributeID of '9875'. If so, then something like this:
CREATE TRIGGER CustomStudent__Trigger_Insert
ON dbo.CustomStudent
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
IF EXISTS(
SELECT 1
FROM inserted i
INNER JOIN dbo.CustomStudent cs ON
cs.personID = i.personID AND
cs.value = '17' AND
cs.attributeID = '9875'
WHERE
i.value = '17' AND
i.attributeID = '9875'
)
BEGIN
/*Duplicate row(s) trying to be INSERTed: log error, send email, etc.*/
/*EXEC sp_send_dbmail ...*/
RETURN;
END /*IF*/
/* No dup 17 & 9875 row was found, so go ahead and insert all new row(s).*/
INSERT INTO dbo.CustomStudent
SELECT
[personID], [enrollmentID], [attributeID], [value], [date],
[customGUID], [districtID]
FROM inserted i
GO
/*end of trigger*/
Edit: Corrected typo.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2016 at 2:07 am
Or create a unique filtered index to avoid the need for a trigger:CREATE UNIQUE INDEX UQ_CustomStudent_personID_value_attributeID
ON dbo.CustomStudent (personID,value,attributeID)
WHERE value = '17'
AND attributeID = '9875'
John
October 6, 2016 at 8:52 am
John Mitchell-245523 (10/6/2016)
Or create a unique filtered index to avoid the need for a trigger:CREATE UNIQUE INDEX UQ_CustomStudent_personID_value_attributeIDON dbo.CustomStudent (personID,value,attributeID)
WHERE value = '17'
AND attributeID = '9875'
John
I didn't go that route because there's no easy way, afaik, to send email, log the error, etc., from a constraint.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2016 at 9:58 am
ScottPletcher (10/5/2016)
Sean Lange (10/5/2016)
Thom A (10/5/2016)
Sean Lange (10/5/2016)
Be careful if you use this code as a starting point. It has scalar values being populated from the inserted table. This is very problematic because the inserted table can have more than 1 row. Triggers need to be set based. In this example using constraints is the right tool for the job and triggers are not. If you are going to stick with this in a trigger you should do your validation on the entire set instead of scalar variables like this example demonstrates.This is exactly why I said I'm not great at them 🙂 From the description, it sounds more like this is a single entry, but dataset based checks(and changing my trigger to do so want be too hard) would be better if multiple values will be passed.
That is something you should be proud of. 😉 Triggers are not really a tool that should be used all that often. I have worked on a fair number of systems that used them regularly and they are a constant source of pain because you forget about them. As a general rule I try to avoid them.
Triggers are a great tool and should probably be used more often for data integrity checks and/or data standardization. The sooner bad data is caught, the easier it is to accurately fix.
Of course triggers must be well written because of their having to execute within the same transaction as the DELETE | INSERT | UPDATE statement.
As with everything, it depends. More often than not triggers are not well written and as such they are not a good way to handle integrity. Of course for integrity I am having a hard time coming up with something that wouldn't be better suited as a constraint. Not saying it isn't there, just saying I can't think of a good example.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2016 at 11:23 am
Sean Lange (10/6/2016)
ScottPletcher (10/5/2016)
Sean Lange (10/5/2016)
Thom A (10/5/2016)
Sean Lange (10/5/2016)
Be careful if you use this code as a starting point. It has scalar values being populated from the inserted table. This is very problematic because the inserted table can have more than 1 row. Triggers need to be set based. In this example using constraints is the right tool for the job and triggers are not. If you are going to stick with this in a trigger you should do your validation on the entire set instead of scalar variables like this example demonstrates.This is exactly why I said I'm not great at them 🙂 From the description, it sounds more like this is a single entry, but dataset based checks(and changing my trigger to do so want be too hard) would be better if multiple values will be passed.
That is something you should be proud of. 😉 Triggers are not really a tool that should be used all that often. I have worked on a fair number of systems that used them regularly and they are a constant source of pain because you forget about them. As a general rule I try to avoid them.
Triggers are a great tool and should probably be used more often for data integrity checks and/or data standardization. The sooner bad data is caught, the easier it is to accurately fix.
Of course triggers must be well written because of their having to execute within the same transaction as the DELETE | INSERT | UPDATE statement.
As with everything, it depends. More often than not triggers are not well written and as such they are not a good way to handle integrity. Of course for integrity I am having a hard time coming up with something that wouldn't be better suited as a constraint. Not saying it isn't there, just saying I can't think of a good example.
The integrity checks I'm considering are mostly those that would not cause rejection of the entire transaction but maybe a correction or just verification later. Many fall into the category of "reasonableness" checks. For example, a last name of a single character: possible but not likely. A quantity of 179,234: again, possible, but perhaps not very likely.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply