473,729 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Create One Trigger For Both Update and Delete

hi,
CAn i have one trigger for both Update and Delete
Delete Trigger
---------------------
create Trigger [tr_delete_user_ log]
on [dbo].[user_log] for delete
as
begin
insert into z_user_log select * from deleted
end

Trigger Update
---------------------
CREATE Trigger [tr_update_user_ log]
on [dbo].[user_log] for update
as
begin
insert into z_user_log select * from deleted
end

Can i have one trigger instead of these Triggers ..

Apr 30 '07 #1
3 7257
On 30 Apr 2007 06:02:57 -0700, satish wrote:
>hi,
CAn i have one trigger for both Update and Delete
(snip)
>Can i have one trigger instead of these Triggers ..
Hi satish,

Yes.

CREATE Trigger [tr_update_delet e_user_log]
on [dbo].[user_log] for update, delete
as
begin
insert into z_user_log select * from deleted
end
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Apr 30 '07 #2
satish (sa************ **********@gmai l.com) writes:
CAn i have one trigger for both Update and Delete
Delete Trigger
---------------------
create Trigger [tr_delete_user_ log]
on [dbo].[user_log] for delete
as
begin
insert into z_user_log select * from deleted
end
As Hugo said, you can. Permit me to point that your example exhibits
two cases of bad practice:

o INSERT without a values list. If someone adds a column to user_log,
the INSERT statement will fail.
o SELECT *. While convenient for ad hoc queries, it's bad in production
code. In this example - if someone adds or removes a column - or
just changes the column order, the INSERT statement will fail. SELECT *
also make it more difficult to find where different columns are
actually used.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 30 '07 #3
On Mon, 30 Apr 2007 21:31:28 +0000 (UTC), Erland Sommarskog wrote:
Permit me to point that your example exhibits
two cases of bad practice:
(snip)

Hi Erland,

Thanks for stepping in. I new feel so bad for not mentioning that
myself. Please remind me not to reply when tired in the future :-)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
May 1 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3536
by: Neil Rutherford | last post by:
During testing of an application, i noticed a difference between SQL 2000 and SQL 7, both with identical config. In a nutshell: A table has a trigger for UPDATE and DELETE. When a column in the table is UPDATED the following happens: In autocommit mode, when entering a trigger the trancount equals 1 for both SQL 7 and 2000.
1
17342
by: efinney | last post by:
Hi, I'm a newbie to sql server and this may be a really dumb question for some you. I'm trying to find some examples of sql server triggers that will set columns (e.g. the created and modified date columns) if the row is being inserted and set a column (e.g. just the modified date column) if the row is being updated. I know how to do this in oracle plsql. I would define it as a before insert or update trigger and reference old and new...
6
6555
by: Scott CM | last post by:
I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed to: SELECT * FROM INSERTED
1
2578
by: Ling Xiaoyu | last post by:
Hello there. Can anybody help me with Postgresql triggers? what I need is a trigger which update value of field "tables_rows.total_rows" to rows count of table "zzz" if I insert new row in table "zzz"... CAREATE TABLE zzz ( ... ... );
1
4461
by: Barbara Lindsey | last post by:
I am a postgres newbie. I am trying to create a trigger that will put a copy of a record into a backup table before update or delete. As I understand it, in order to do this I must have a function created to do this task. The function I am trying to create is as follows: CREATE FUNCTION customer_bak_proc(integer) RETURNS boolean as 'INSERT INTO customer_bak (SELECT * from customer where id = $1 )' LANGUAGE 'SQL';
1
2428
by: Bruno BAGUETTE | last post by:
Hello, I'm trying to build a PL/PGSQL function that will be called by a trigger which will update a table named 'mview_contacts'. That table plays the role of a materialized view. The 'plpgsql' language is installed on that database (there are already several functions inside) but I don't understand why I can't store that function in the database.
27
3787
by: max | last post by:
Hello, I am a newbye, and I'm trying to write a simple application. I have five tables with three columns; all tables are identical; I need to change some data in the first table and let VB updates the same data in all other four tables in the right places. I know it would be possible by using the ForeignKeyConstraint object. I have created the tables using the DataSet Visual Tool and I know it doesn't create any ForeignKeyConstraint obj....
3
3906
by: Yas | last post by:
Hi everyone I am trying to create a DELETE Trigger. I have 2 tables. Table1 and Table2. Table 2 has all the same fields and records as Table1 + 1 extra column "date_removed" I would like that when a record is deleted from Table 1, the trigger finds that record in Table2 and updates the date_removed filed with current time stamp. The primary key on both is combination of domain,admin_group and cn.
6
4320
by: Alvin SIU | last post by:
Hi all, I have a table in Db2 v8 like this: Team Name Role ------ -------- --------------------- A Superman Leader A Batman Member A WonderWoman Member B Alvin Leader
0
8927
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9289
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9210
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9156
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8159
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4534
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4798
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3242
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2166
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.