Insert or Update statement on a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert or Update statement on a table

Author

Topic

tvb2727
Starting Member

35 Posts

Posted - 2010-10-17 : 14:40:18

Is there a way a trigger knows if an update or insert statement is being processed on a table?

If (update statement = true)

process an update statement

else
process an insert statement

OR

is there a way just to tell it to go ahead and perform the update or insert statement after some checks and the data is validated?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-17 : 15:46:52

Most data validation should happen before the DML, however you can do this inside an AFTER trigger.

You can quickly check if it's an update by seeing if there are rows in the deleted trigger table, otherwise it's an insert. You can also use the UPDATE() function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-17 : 15:54:02

Is there a way a trigger knows if an update or insert statement is being processed on a table?

Two ways:
You can create a trigger FOR UPDATE and it will be fired on UPDATE.
You can create another trigger FOR INSERT and it will be fired on INSERT.
OR
You can create a trigger FOR UPDATE,INSERT and it will be fired on both insert or update.
Then you have to count: if inserted has rows AND deleted has rows then it was an update. If only inserted has rows then it was an insert.

is there a way just to tell it to go ahead and perform the update or insert statement after some checks and the data is validated?
If the trigger checks and the data is valided then the trigger has nothing to because the data is already inserted or updated.
If the trigger checks and the data is not ok then the trigger can do a raiserror to give an error message and then a rollback.

Read here and you will learn a lot about triggers:
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

No, you're never too old to Yak'n'Roll if you're too young to die.

tvb2727
Starting Member

35 Posts

Posted - 2010-10-17 : 16:41:11

The reason I ask, is because last night i had to write a trigger and i got it working with a INSTEAD OF besides a FOR. To make it work though I had to put the update statement in the else of the if statement. I have to write a trigger to verify the date is not in the future and it works to validate that the date is not in the future, but does not update the table with the new date. What I am doing wrong?

UPDATE Statement:

UPDATE TRUCK
SET TRUCK_BUY_DATE= '10/4/2010'
WHERE TRUCK_NUM = 1027

TRIGGER:

Create TRIGGER TRIG2
ON TRUCK
FOR INSERT, UPDATE
AS
declare @v2 smalldatetime

select @v2 = inserted.TRUCK_BUY_DATE from inserted
Print @v2
Print GetDate()
if (@v2 > getdate())
begin
rollback
RAISERROR ('Date Is greater than todays Date', 16, 10)
end

The @v2 is pulling in the date that's already in the table under truck_num = 1027

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-17 : 19:10:45

You need to use an INSTEAD OF trigger for this, which means you'll have to validate the data and then perform the DML query (UPDATE/INSERT).

I think you can do this in a check constraint though. Or even before you send the DML query, validate it in the application. Why do you have to use a trigger?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tvb2727
Starting Member

35 Posts

Posted - 2010-10-17 : 22:53:30

Cannot create trigger 'TRIG2' on table 'TRUCK' because an INSTEAD OF UPDATE trigger already exists on this object.

thats the error i get when I do the INSTEAD OF. I do have another trigger that has an INSTEAD OF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-18 : 12:32:51

As the error indicates, you can only have one INSTEAD OF trigger per table. You'll need to use ALTER TRIGGER to add your new logic to the existing trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-18 : 15:31:47

Read about the MERGE statement in BOL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

prabhakar_1432
Starting Member

5 Posts

Posted - 2010-10-19 : 05:54:19

Use can use the Stored Procedure.

Just Checking the flag

If flag = 'Save'
Record inserted here.
else
Record updated here.

Pborade

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources