T-sql trigger

Transact-SQL
1

In a server database, I would like to setup a trigger on a field called sutdata fort size varchar(50).

The trigger needs to be setup when itfid ='998gf'. .

Would you show me the trigger sql needed?

2

Jassie1,
You need to provide move information. What condition(s) cause the trigger to fire? What is the DDL for the table? What do you want to happen when the trigger is fired?

1 Like
3

In response to your question,

  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 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] ([enrollmentID])
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] ([personID])
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] ([attributeID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CustomStudent] CHECK CONSTRAINT [FK_StudentData_StudentAttribute]
GO

4

You need to go about this with slightly different thinking.

You can set up a trigger to "fire" if a row to a table is Inserted or Updated (in your case, but it is also possible for the trigger to fire on a Delete)

When the trigger fires you have two pseudo tables available [inserted] and [deleted], which represent the rows being changed. For an insert action there will be nothing in the [deleted] table, for an update both [inserted] and [deleted] will contain data - in effect the "after"/new data and the "before"/old data. You can compare rows between these two tables to see if the [value] column has been changed inappropriately.

It is important to note that you need to build your trigger assuming that [inserted] and [dleted] contain multiple rows. The trigger does NOT fire for each row changed, but rather once for each INSERT / UPDATE / DELETE statement. If it is "impossible" for your application to process more than one row at a time, and thus you build your trigger to only handle a single row, then it is important to also build in a ROLLBACK if the trigger ever encounters multiple rows - 'coz it plain will not work if it is built to only handle one row!

5

maybe a trigger is not what you are looking for? what if you just prevent the duplicate insert in your sproc that inserts/updates data and then capture the attempted dup row insert/update into a logging table? then a sql job that runs hourly that captures these rows from your logging table and send email accordingly?

6

Hmmm ... might be that a CONSTRAINT will do, but if not, personally, I'd prefer a TRIGGER so that no [future] code can slip an invalid value into my (pristine!!) table. Well meaning (maybe!) people "just importing some data from an XLS" get seriously in the way of an SProc full of lovely data-validation-checking, house-rule-enforcing, code :frowning:

7

omg I am determined to destroy excel from the face of this planet (no chance) and why in the world are people importing excel data into data?!? :frowning: :scream:

8

Beats me ... but such folk clearly think that its a good idea.

Last time someone did that Excel removed all the leading zeros from their product codes (Excel thought they were numbers ... and, no, I don't know why the client thought it was a good idea to have Product Codes with leading zeros ...). Anyway, the Upshot was that their database was Hosed !!