NEED TO ADD COMMENTS ON WHAT EACH PIECE OF SQL CODE DOES
CREATE TABLE Production.ProductAuditTrail
(AuditID INT IDENTITY(1,1),
AuditDate DATETIME NOT NULL,
ChangeUser SYSNAME NOT NULL,
ProductID INT NOT NULL,
BeforeListPrice MONEY NOT NULL,
AfterListPrice MONEY NOT NULL)
GO
CREATE TRIGGER tu_ProductAuditTrail
ON Production.Product
FOR UPDATE
AS
INSERT INTO Production.ProductAuditTrail
(AuditDate, ChangeUser, ProductID, BeforeListPrice,
AfterListPrice)
SELECT GETDATE(), SUSER_SNAME(), i.ProductID, d.ListPrice,
i.ListPrice
FROM inserted i INNER JOIN deleted d ON i.ProductID =
d.ProductID
GO
SELECT * FROM Production.ProductAuditTrail
GO
UPDATE Production.Product
SET ListPrice = ListPrice + 1
WHERE ProductID = 514
GO
SELECT * FROM Production.ProductAuditTrail
GO
CREATE TABLE Production.ProductAuditTrail
//Production is database name and ProductAuditTrail is a
table that has to be created in that DB.
(AuditID INT
IDENTITY(1,1), // A SQL Server
IDENTITY column is a special type of column that is used
to automatically generate key values
based on a provided seed (starting point) and
increment
AuditDate DATETIME NOT NULL, //Date and time in
YYYY-MM-DD HH:MI:SS format
ChangeUser SYSNAME NOT NULL, // SYSNAME is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
ProductID INT NOT NULL,
BeforeListPrice MONEY NOT NULL,
AfterListPrice MONEY NOT NULL) // The
money data type is an abstract data
type. Money values are stored significant
to two decimal places. These values are rounded to their amounts in
dollars and cents or other currency units on input and output, and
arithmetic operations on the money data type
retain two-decimal-place precision.
CREATE TRIGGER tu_ProductAuditTrail
ON Production.Product
FOR UPDATE // triggers are special stored procedures that are executed automatically in response to the database object, database, and server events. Here there is a trigger on the Production.Product table when the table is updated.
AS
INSERT INTO Production.ProductAuditTrail
(AuditDate, ChangeUser, ProductID, BeforeListPrice,
AfterListPrice)
SELECT GETDATE(), SUSER_SNAME(), i.ProductID, d.ListPrice,
i.ListPrice
FROM inserted i INNER JOIN deleted d ON i.ProductID =
d.ProductID //
Triggering condition for the table tbased on ProductID when
insertion and deletion is taken place
SELECT * FROM Production.ProductAuditTrail // lists out the records for all the columns in Production.ProductAuditTrail table
UPDATE Production.Product
SET ListPrice = ListPrice + 1
WHERE ProductID = 514
//Updates the Production.Product table by setting
ListPrice = ListPrice + 1 where ProductID=514.
SELECT * FROM Production.ProductAuditTrail // lists out the records for all the columns in Production.ProductAuditTrail table
NEED TO ADD COMMENTS ON WHAT EACH PIECE OF SQL CODE DOES CREATE TABLE Production.ProductAuditTrail (AuditID...
Use the Northwind database. Choose the best answer from those given below. Sometimes a database may have a separate table to log certain activities taking place in the database. Create a new table named ‘ChangeLog’ in the Northwind database as follows: ChangeID int identity(1,1) primary key EmpID int (will contain the ID of the employee being changed) User nvarchar(30) (will contain the login of the user making the change) Date smalldatetime (will contain the date of the change) OldRate money (will contain the old payrate of the employee) NewRate money (will contain...
2. Examine the SQL Script for this test. Explain the purpose of each of the stored procedures listed below. Test2SP1 Test2SP2 Test2SP3 Test2SP4 Test2SP5 Test2SP6 DATABASE CODE: if db_id('Test2PremierProducts') is not null begin use master alter database Test2PremierProducts set SINGLE_USER with rollback immediate drop database Test2PremierProducts end create database Test2PremierProducts go USE Test2PremierProducts; go /* create the tables */ CREATE TABLE Rep (RepNum int Identity(10,10), LastName VARCHAR(15), FirstName VARCHAR(15), Street VARCHAR(15), City VARCHAR(15), State VARCHAR(2), Zip VARCHAR(5), Commission MONEY, Rate DECIMAL(3,2),...
--Create procedure for GetPartNums in SQL --GetPartNums- retrieve all part nums from the database --Database if db_id('TestPremierProducts') is not null begin use master alter database TestPremierProducts set SINGLE_USER with rollback immediate drop database TestPremierProducts end create database TestPremierProducts go USE TestPremierProducts; go /* create the tables */ CREATE TABLE Rep (RepNum int Identity(1,1), LastName VARCHAR(15), FirstName VARCHAR(15), Street VARCHAR(15), City VARCHAR(15), State VARCHAR(2), Zip VARCHAR(5), Commission MONEY, Rate DECIMAL(3,2), PRIMARY KEY (RepNum)); go CREATE TABLE Customer (CustomerNum int Identity(1,1) PRIMARY...
Task1: Construct and run the code for a natural inner join on the two tables AccountState and AccountUserState Task 2: Construct and run the code for a natural inner join of AccountState, AccountUserState, ClientProperty Task 3: Construct and run the code for Right outer join on ClientProperty and AccountUserState Task 4: Construct and run the code to sum the account totals for each branch. Construct your own database for this and enter the data. AccountNr, BranchNr, ClientNr...
1. This trigger is from Figure 16.2 of the text. Copy and run this trigger so it is created in the ap database. You can run the UPDATE below to test it. Notice two things. The error message appears as a pink screen just like a syntax error would. Also, if you then query the invoices table, the UPDATE statement did not execute. The trigger prevented the erroneous UPDATE from changing the data. DELIMITER // CREATE TRIGGER invoices_before_update BEFORE UPDATE...
Step 1: Create table audits via triggers The system must log any insertion, deletion, or updates to the following tables: • Employee table (project 1) create table Employee ( empNumber char(8) not null, firstName varchar(25) null, lastName varchar(25) null, ssn char(9) null, address varchar(50) null, state char(2) null, zip char(5) null, jobCode char(4) null, dateOfBirth date null, certification bit null, salary money null, constraint PK_EMP PRIMARY KEY(empNumber), constraint EMP_STATECHECK CHECK(state in ('CA','FL')) ) GO • Job table (project 1) create...
Part I. Create a library check-out database using Microsoft SQL Server that stores data about books, patrons, and the check-out process. Books (BookID, BookName, Author, YearPublished) Patrons (PatronsID, PatronsName, PatronsAddress, PatronsBirthday) CheckInOut (TransactionID, PatronID, BookID, CheckOutDate, NumDay, ReturnDate, Late, Fees, Paid) - the NumDay field contains the number of days patrons can keep the book, if the return date is over the number of day, then the Late field will have a Y value and a fee of $1.00 per...
PL/SQL Auction Program 1. Create a user xyz, who is the owner of the auction. Create the schema, and package. 2. Create users x1 and x2 who are the participants in the auction. They will need acces to the package. 3. Bid on the same item and record your observations. Verify all scenarios. Upload the files with the missing code and a detailed sample run. AUCTION OWNER.TXT SQL> conn / as sysdba Connected. SQL> drop user xyz cascade; User dropped....
need help fixing this code. also need a screenshot of it running thank you /* Creating table. */ CREATE TABLE HOM(story_name varchar (255), [user] char(50), age int, reading_counts int NOT NULL, CONSTRAINT Story_User PRIMARY KEY (story_name,[user])); INSERT INTO HOM VALUES ('Humpty Dumpty', 'Andrea', 5, 10); INSERT INTO HOM VALUES ('Wheels on the Bus', 'Simon', 3, 15); INSERT INTO HOM VALUES ('Baa Baa Black Sheep', 'Leo', 7, 4); INSERT INTO HOM VALUES ('Jack and Jill', 'James', 4, 11); INSERT INTO HOM...
Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB4DB; USE LAB4DB; CREATE TABLE CUSTOMER ( CUS_CODE int primary key, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0'); INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86'); INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75'); INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0'); INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0'); INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19'); INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93'); INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55'); INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0'); /*...