Database objects Database users Roles Database files Database properties Code (d

Question

Database objects

Database users

Roles

Database files

Database properties

Code (database)

Code (users)

Code (roles)

Use syntax to Create

Create a trigger
the trigger should select column values from deleted add system_user and date and insert values into history table. It does not matter if the table was updated or record was deleted, in both cases old data will be kept in deleted

insert at least two records in the Address table

update the first record

delete the first record

Using syntax

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] } <dml_trigger_option> ::= [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]

Another Syntax

CREATE TRIGGER [ schema. ] trigger_name

ON { table | view }

[ WITH <dml_trigger_option> [ ,...n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier [ ; ] > }

Another SYntax

CREATE TRIGGER withdrawTrigger

ON account INSTEAD OF UPDATE

AS

    DECLARE @balance_after money;

    DECLARE @account int;

    SELECT @balance_after = (SELECT balance FROM INSERTED) ;

    SELECT @account= (SELECT accountID FROM INSERTED);

    print cast(@balance_after as varchar(20))+ ' ' + cast(@account as varchar(20));

            -- cast is used to convert one data type to another one

    IF (@balance_after < 0)

         print 'Non-sufficient funds';

    ELSE

      BEGIN

          print 'Balance was updated';

          UPDATE account SET balance = @balance_after

                 WHERE accountID = @account;

      END

GO

Using either of these Syntax please

type count tables 70 views 17 indexes 169 procedures 9 functions 11 all columns 725 all parameters 47 types 6 triggers 12 defaults 147 schemas 18 assemblies 1

Explanation / Answer

Assumptions : History table name - [Person].[AddressHistory]

History table has all columns same as [Address] table with SystemUser & CreateDate as additional columns.

Change History table name and columns in the script, If it is different from my assumptions.

Explanation :

A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.

A trigger executes when a user tries to INSERT, UPDATE, or DELETE statements on a table or view.

Here we are creating a trigger which will execute after any UPDATE or DELETE on Address table.

Server internally use "deleted" table to hold old record of the table,

it automatically structures the "deleted" table similar to the table on which the trigger is defined.

Script :

CREATE TRIGGER [Person].[AddressTrigger]  

ON [Person].[Address]

AFTER UPDATE, DELETE

AS  

Insert Into [Person].[AddressHistory] ( AddressID,

AddressLine1,

AddressLine2,

City,

StateProvinceID,

PostalCode,

rowguid,

ModifiedDate,

SystemUser,

CreateDate)

Select AddressID,

AddressLine1,

AddressLine2,

City,

StateProvinceID,

PostalCode,

rowguid,

ModifiedDate,

SYSTEM_USER,

GETDATE()

From deleted

Post Script: You have to Insert 2 records in Address table, Update one and delete the other one.

Then check "AddressHistory" table, it should have 2 records same as you Inserted at first(i.e. Data before update).

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.