Easy trigger? 3

[nevergiveup01]

Hello, I'm trying to create a trigger that will copy the CustID from the customer table to the tblServiceQuote table whenever a new record is added to the customer table.. I'm getting the error message: the parameter is incorrect.. What am I doing wrong? See below..





ALTER TRIGGER customer
ON tblcustomer
FOR insert
AS
BEGIN
INSERT INTO tblServiceQuote (CustID)
Select CustID
from inserted

END

 

[SimonEireconsulting]

why use a trigger???

If you use a stored procedure to do the insert into the customer table you can also use this stored procedure to do the insert into the tblServiceQuote table, have the two inserts inside a transaction, then you don't get customer records without tblServiceQuote records:

CREATE PROCEDURE [dbo].[sAddCustomer]
@CustomerName varchar(100)
-- other parameters here
AS
DECLARE @CustomerID int
BEGIN TRANSACTION
INSERT INOT tblCustomers (CustomerName,...)
VALUES (@CustomerName,...)
SET @CustomerID = @@IDENTITY
INSERT INTO tblServiceQuote (CustomerID,...)
VALUES (@CustomerID,...)
COMMIT TRANSACTION

GO

 

[nevergiveup01]

Cool thanks!

 

[SQLSister]

No never use @@identity!!!!!! It wil cause data integrity problems. @@identity will not give the correct value if there is a trigger on the table that inserts to another table that has an identity field.

Use scope_identity instead as it will not fail you when someone adds a trigger.

I also disagree with the advice to use a sp. If the other table must always be populated a trigger is the correct place for it. Not all records are always created frpom the user interface. A trigger ensures data integrity. Data integrity should be maintained from triggers and constraints and not from sps.

Your trigger looks fine by itself, I wonder if there are other existing triggers on the table that are somehow being affected by this one? Another possiblity is that there are other required fields on that table which you are not sending information to fill.

"NOTHING is more important in a database than integrity." ESquared

 

[SimonEireconsulting]

SQLSister,
When you say "@@identity will not give the correct value if there is a trigger on the table that inserts to another table that has an identity field.", this confuses me.

Does @@IDENTITY not hold the ID of the record that process has just modified / added / deleted??

I am suggesting using this method rather than using a trigger, so "if there is a trigger on the table" would not apply. Yes, granted, if there are "other processes" that add customers to the table then these other processes will need to add tblServiceQuote records at the same time.

 

[gmmastros]

SimonEireconsulting,

Here is a sample script that highlights the problem with @@Identity.

[green]-- Create a new database to test this[/green]
Create Database TestTriggers
go
[green]-- use the new database[/green]
Use TestTriggers
go

[green]-- create a table[/green]
Create Table Child(Id Int Identity(1,1), Data VarChar(20))
go
[green]-- Create a parent table[/green]
Create Table Parent(Id Int Identity(1,1), Data VarChar(20))
go
[green]-- Create a trigger on the parent table[/green]
  CREATE TRIGGER tg_Parent
     ON  Parent
     AFTER INSERT
  AS 
  BEGIN
	  SET NOCOUNT ON;
  
	  Insert Into Child(Data)
	  Select Data From Inserted
  
  END
go
[green]-- Insert a row in to the child table.  This will cause the identity column to be 1
-- There are no triggers on this table, so @@identity and Scope_Identity() return the same value[/green]
Insert Into Child(Data) Values('Blue')
Select 'No trigger, both return same value', @@Identity As [Identity], Scope_Identity() As [Scope Identity]
go
[green]-- Insert a row in to the parent table.  
-- Since there is a trigger on this table that inserts in to the child table,
-- the @@Identity will return the value inserted in to the child table
-- and Scope_Identity will return the value used in the parent table[/green]
Insert Into Parent(Data) Values('Purple')
Select 'Trigger on this table, identity <> scope_identity', @@Identity As [Identity], Scope_Identity() As [Scope Identity]

go
Use Master
go
Drop Database TestTriggers

When inserting in to the child table, there are no triggers and @@Identity returns the same value as Scope_Identity(). When inserting in to the parent table, the trigger kicks in and inserts a row in to the child table. @@Identity will return the value from the Child table, and Scope_Identity() will return the value from the parent table.

The bigger problem here is.... There could be multiple triggers on the parent table, inserting values in to various child tables, each with their own identity column. So... which @@Identity will you get? Who knows. Ya know.

Or, you could be using @@Identity, and everything works fine for a while. Fast forward 6 months, and someone else decides to add a trigger to the table. Suddenly, your @@Identity process is not returning the correct values, but you probably wouldn't notice for a while. Not until the end user comes to you and says, "the data is wrong. I entered a phone number for client X, but it's showing up under Client Y instead". Of course, it's the stinking @@Identity that caused the problem, but it'll probably take a long time to find it, 1/2 a minute to fix it (use Scope_Identity() instead), and then a year or 2 to fix the bad data that @@Identity caused.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SimonEireconsulting]

of course this makes sense - none of it is relevant DEPENDING on your development guidlines in your company.

I was making the case for using TRANSACTIONS in stored procedures, and NOT using triggers, so the above would just not be relevant.

I have used SQL Server since 2000, NEVER used triggers, always used stored procedures with transactions and NEVER had data integrity issues.

 

[gmmastros]

All I'm saying is, it's best to use the right tool for the job.

You wouldn't mow your lawn with a pair of scissors.
You wouldn't shovel your driveway with a soup spoon.

You also shouldn't use @@Identity to get the identity value.

Similarly, if there's a particular task well suited for a trigger, then you should use a trigger.

For example, I have a heavily used table in my DB, with lots of inserts, updates & deletes. I needed to add some logging to this table. A relatively simple trigger took care of this completely. It's quicker to write a single trigger than it is to modify SP code when the code could be spread over a dozen SP's. It's safer too because I don't have to worry about missing a procedure, or adding a procedure later on. The trigger will always be there and will always do the right thing.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

SimonEireconsulting, even if you never use triggers there is no guarantee someone down the road will not add one, especially an audit trigger. So it is dangerous to ever use or to suggest to someone else to use @@identity when scope_identity does the same thing and can never have the same problem occur.

Curious as to why you dislike triggers so much. Correctly written they ensure business rules are followed by everyone even by the new programmer who doesn't know that stored proc exists (or even that the business rule exists) or the person who does an import through SSIS or DTS, or the person asked to do the occasional ad hoc query directly on the database (please insert these 200000 new customers from our merger with company b so we don't have to manually enter them, for example). Why wouldn't you want the protection on your database of knowing that no matter how data is brought in, the rules will be enforced?

"NOTHING is more important in a database than integrity." ESquared

 

[danvlas]

"Use scope_identity instead as it will not fail you when someone adds a trigger."

Well, in a particular case it will. If the trigger is an 'instead of insert' one.

In this case, the Insert is actually done within the trigger, which is out of the scope of the sp, therefore scope_identity will not return the correct value.

@@identity will do its job, but only if there are no other triggers to change the value afterwards.


Here is what MS says (

http://msdn.microsoft.com/en-us/library/ms175098.aspx):

(quote)
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
(end quote)


I stand corrected if I'm wrong.

Actually, for this particular reason I'd be extremely happy to learn how I can cancel an Insert by checking the validity in an 'After Insert' trigger, but not by issuing an explicit ROLLBACK TRANSACTION within the trigger.




Daniel Vlas
Systems Consultant

 

[Thargy]

sqlsister,

I too don't like triggers. They are a way for something to happen automagically, behind the scenes. RI enforced by triggers is really no RI at all.

If a key value is inserted by a trigger, it's only valid at the time the trigger fires. If an FK values is subsequently altered, the db will do nothing about legacy values (since they won't cause a trigger to fire) and will silently accept inconsistent values into the db.

The system itself should maintain integrity, that's what FK's were put there for in the first place. As George has said above, you wouldn't sweep your drive with a soup spoon. The db has keys for RI for a good reason. Using the wrong tool, i.e. triggers is a mistake.

Regards

T

 

[SQLSister]

The fact that they fire behind the scenes automatically is exactly why they are necessary. It means data integrity will be enforced even when the developers have to run a quick update statment to inclease all prices. It means data integrity will be enforeced when two or more different user interfaces access the same data. Audit triggers have caught people committing fraud by changing the data outside the normal GUI.

I didn't say you should use triggers in place of fks. However, it is extremely dangerous to enforce data integrity outside the database as there are many many ways to put data into the database. A trigger is the best place for enforcing data integrity rules that cannot be enforced through constaints or relationships. If you have ever had to clean up the mess people made by not using triggers when they are the best tool for the job, you would be far more inclined to prefer triggers. (and by the way if you design properly, if the values change in an update and not just an insert, a trigger could still be designed to fire.)

"NOTHING is more important in a database than integrity." ESquared

 

[Thargy]

I concur with maintaining data integrith within the db, that's the only place it can be done. I always envisage a sp interface to the outside world, so that only legitimate changes can be made.

I've cleared up plenty of messes myself, so I know of which you speak. However, I still disagree with the triggers. Many moons ago I wrote a trigger-based audit for every table in a db, with the intent of making an unavoidable log of activity. It worked, but was never used. However, if you use stored procedures, the audit cannot be avoided. The entry of data in a table should add an audit entry, as part of that stored procedure. Allowing multifarious edits by means other than the sp interface is obviously forbidden.

Those wanting access to data can gladly have a read-only account, but if you want to modify data, you have to do it via the controlled route. If you design properly, audit is part of the original requiremenst, and not bolted on as an afterthought via triggers.

I feer I veer towards a "General Database Discussion" heer, so be of good cheer, as Christmas is neer. I'll desist in the interests of us all having a relaxing time, starting on Thursday.

Nice to see you alive 'n kickin' there SQL Sister.

Regards

T

 

[gmmastros]

Allowing multifarious edits by means other than the sp interface is obviously forbidden.

Forbidden by whom? Enforced how?

Let's use auditing as a discussion point. If you do all your auditing in stored procedures, then you will need to modify all stored procedures that modify data so that they do the proper auditing, right? The problem is... what if you forget to add the auditing code to a new stored procedure? Then what? Your audit tables will be missing data.

Since there are usually a lot less tables in a database than there are stored procedures, it seems better to put the code in a trigger instead of scattering it about with stored procedures.

I think the problem many developers have with triggers is based on 'stinkin thinkin'. Some of the terms you have used are: automagical, sp's are legitimate (therefore triggers are ill-legitimate), and "Bolted on as an afterthought".

Used properly, and appropriately, triggers are the best solution for certain problems. Unfortunately, there are plenty of examples where trigger code is badly written, which gives them a bad name. Use them for what they were designed for, and they solve a variety of problems, with the most efficiency and consistency. Just don't abuse them, or you'll be sorry.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Thargy]

George,

You get your noble steed and shield ready, and I'll pretend to be a dragon, ok? (I'm slipping into slayerishness here, so be sure your sword is sharp and your lance, is well, er, umm, ready, yes, that's it, ready.

I intend that all users will log on via one account, which will only have permissions to execute sp's. Those sp's will move the db from one valid business state to another, thereby completing a business process.

Since direct access to tables will be unavailable, only legitimate activities can be carried out. Assuming the designer had the wit to isolate different functions, a beneficial side-effect, is that the dba knows that any and all code which affects a particular table is in one place, and one place only.

If you forget to add the auditing code to your new sp, then that's called sloppy programming, and in any event, must surely fail unit test. Since audit was designed in from the start, correct audit entries will be part of all unit tests, right?

W.R.T scattered audit, I would implement procedures to provide audit of a table, based on passing in say table name, and the pk of the affected rows(s). All the audit sp's would be named to show their purpose, and be similar in nature. The business processes would obtain audit by invoking these procedures, not by having tons of code in each sp. However, this is very poor as it is merely data audit - see below.

Putting a trigger on each table to modify an audit table, gives nothing more than a data audit. Most companies are interested in business audit. They want to know who did what, when, and involving how much money. I first got into db's when I wrote an Access database to reverse engineer an oracle trigger-based data audit into meaningful business transactions. It soon became apparent that only two or three people in the company has sufficient knowledge to follow a sequence of updates, inserts and delete audits through a dozen tables, and mentally convert them into something useful. I'd be happy to be wrong, but I don't see how a trigger on a table can do anything other than provide data audit.

Regards

T

 

[gmmastros]

In the spirit of the holidays.... Let's put down our swords and pick up a candle instead. Let's agree to disagree.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Our audit tables are all populated from triggers and they all provide who did what when information.

"NOTHING is more important in a database than integrity." ESquared

 

[SimonEireconsulting]

thargtheslayer,

I completely agree with you - this was my point all along.

SQLSister said in a post "there is no guarantee someone down the road...". Well, I disagree. There IS a guarantee that this will NOT happen, as these are exactly the development guidelines that we use: all access to data is restricted to stored procedures. These stored procedures have audit logs built in, maintain RI,...

gmmastros - you say "what if you forget to add the auditing code " - what can I say apart from "what if you forget to add the trigger to do the audit?"

MERRY CHRISTMAS

 

[gmmastros]

To each their own. If you chose to ignore triggers, that's your choice.

I have no intention to get in to a big debate regarding triggers. I happen to think they have their uses, but that's just my opinion.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[danvlas]

I totally agree with George.
Everyone knows best what they need.

Merry Christmas Everyone!



Daniel Vlas
Systems Consultant