Click here to Skip to main content
15,746,302 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone
I have a question about trigger in sql-server. I have a table like this
SQL
create table STUDENT
(
id   char(10) primary key,
sname char(50)
)

create table AUTHEN
(
us  char(10) foreign key references STUDENT(id),
pw char(50)
)


So now i want when i insert into STUDENT a new row, the trigger will help me to insert into AUTHEN a new row with 'us' is the value 'id' i just inserted into STUDENT
Could anyone help me, please

Dear
Clack
Posted
Updated 11-May-11 16:23pm
v3

it is just a sample code(not tested) to get you started. Hope it helps

SQL
CREATE TRIGGER [ schema_name . ]trigger_name
ON table_name
AFTER
INSERT
AS
@Username = select id from inserted
insert into AUTHEN(us,pw) values(@username,'password')
go
 
Share this answer
 
Comments
nguyenle.it 12-May-11 4:22am    
This is the best i got. Thank you so much!!!
CS2011 12-May-11 4:30am    
You are always welcome. :-)
Create trigger triggername
On STUDENT
AFTER INSERT
AS
BEGIN
insert into AUTHEN(us,pw) values(select id from inserted,'pwd')
END
 
Share this answer
 
Comments
nguyenle.it 12-May-11 4:23am    
Thank you so much, i got it.
Creating Email Triggers in SQL Server 2005

What exactly is a trigger?



It is a piece of SQL that is activated when a certain event happens or occurs. A common usage is when a new record is added to a database, this triggers an action - for example, a new member triggers an email to the admin for the registration. Or you have certain records in a database table that are assigned to a particular customer and, upon that record being updated, that customer/admin should be notified of the change.



Triggers are useful to fire events and perform some action when data is inserted, updated, or deleted from a table. Microsoft SQL Server's xp_sendmail stored procedure can be invoked to send an email to one or more recipients.



Creating the Table



We will start by creating a table which will hold Customer Information that need to be updated. The name of the table is CustomerInfo









or you can use the following Sql Script



CREATE TABLE [CustomerInfo]

(

[CUSTOMER_ID] [int] IDENTITY(1,1) NOT NULL,

[FIRST_NAME] [varchar](50) NULL,

[MIDDLE_NAME] [varchar](50) NULL,

[LAST_NAME] [nvarchar](50) NULL,

)

GO





Inserting the Data



Now, we will insert some dummy data into the table. You can directly insert the data into the table by using the Grid Format or you can just write the script to do that.






or you can use the following Sql Script





INSERT INTO [CustomerInfo]([CUSTOMER_ID],[FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES (1,'Amjad','Ali','Leghari')



INSERT INTO [CustomerInfo]([CUSTOMER_ID],[FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES (2,'Ali','Raza','Shaikh')





Creating the Trigger



Now coming down to the business, we will create the trigger for the CustomerInfo table, but there are few steps that need to be checked before creating the trigger.



Check that the trigger already exists
If the trigger already exists, then delete it



If the trigger doesn’t exists, now you can create the specified trigger. Right click on the Trigger section and click on “New Trigger”.






This will generate the following template.





-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Create date:

-- Description:

-- =============================================

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>

ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>

AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;



-- Insert statements for trigger here



END

GO



So, now we end up writing the following trigger



-- Create Trigger with name 'CustomerUpdateMail'

CREATE TRIGGER CustomerUpdateMail

-- and the table is CustomerInfo

ON CustomerInfo

-- trigger is fired when an update is made for the table

FOR UPDATE

AS

-- holds the CustomerID so we know which Customer was updated

declare @CustomerID varchar(10)



-- holds the body of the email

declare @body varchar(2000)



-- holds the old customer name which has been changed

declare @CustomerName varchar(10)



-- holds the new customer name

declare @CustomerNewName varchar(10)



-- gets the previous customer first name that was deleted

SELECT @CustomerID = Customer_id,

@CustomerName = d.First_Name

FROM deleted d



-- gets the new customer first name

SELECT @CustomerNewName = First_Name

FROM inserted



SET @body = 'Customer with ID=' @CustomerID ' has been updated

with previous First Name is ' @CustomerName '

and the new First Name is ' @CustomerNewName



--xp_sendmail is the extended sproc used to send the mail

EXEC master..xp_sendmail

@recipients = 'ali_raza_shaikh@datasprings.com',

@subject = 'Customer Information Updated',

@message = @body

GO



So, after writing the trigger, execute it to create the trigger and you can see the newly created trigger under the trigger section.



image010.jpg



xp_sendmail extended stored procedure



Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message. In order to use this extended stored procedure you must have SQL Mail Setup. You must grant EXECUTE permissions for the xp_sendmail procedure in the master database. After completing all these steps, the following lines will send the mail to the target recipient.





--xp_sendmail is the extended sproc used to send the mail

EXEC master..xp_sendmail

@recipients = 'ali_raza_shaikh@datasprings.com',

@subject = 'Customer Information Updated',

@message = @body





Testing the Trigger



After the table has been created, we inserted the dummy data into the tables and in the end created a trigger to mail the user about the customer update, now its time to execute the trigger. You can execute the trigger by using the following lines.





update customerinfo

set First_Name='Ali Update'

where customer_id=1

///////////////////////
check this link
http://www.datasprings.com/resources/articles-information/creating-email-triggers-in-sql-server-2005[^]
 
Share this answer
 
 
Share this answer
 
Comments
mafnx 30-Dec-11 5:33am    
Hey all, I'm developing an Oracle database -I'm kinda new to oracle-, and I want to use triggers. I'm using oracle 11g, I have a table called Computer. When I run the below trigger, it debugs and gives no errors, BUT! once I try to insert any data into Computer, it fires an error and says I have to either drop, or disable the trigger, or I have privileges/authorization issues!


CREATE OR REPLACE TRIGGER "MyUser"."TRG2TEST"
BEFORE INSERT ON COMPUTER
DECLARE NEW AS NEWROW
BEGIN
INSERT INTO COMPUTER (COMPID,COMPNAME,CPU,hdd_size,motherboard,operatingsystem,visible,good) VALUES ((select max(CompID) from IDZ)+1,newRow.CompName,newRow.CPU,newRow.HDD_Size,newRow.MotherBoard,newRow.OperatingSystem,1,1);
END;

Please note that I tried this code using Oracle SQL Developer.
when I go to SQL-Plus to run this code (As system), it always stops at the name of the table: "Computer"
I really need your help. Thank you in advance!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900